LIKE query in related table

Question

LIKE query in related table

Navigation

#1 by (3 votes)

1

I have a query that does the search for TECHNICAL CALLS and in this research I make some JOINS to bring related information. I need LIKE to also work for the CLIENT name (which is in another table)

The query I have is this:

SELECT *,
       't1'.'usuario' AS nome_usuario_responsavel,
       't1'.'id_usuario' AS id_usuario_responsavel,
       't3'.'usuario' AS usuario_criador_chamado
FROM (
       'usuarios' t1,
       'usuarios' t3,
       'chamados'
)
INNER JOIN 'clientes' ON 'chamados'.'fk_cliente_chamado' = 'clientes'.'id_cliente'
LEFT JOIN 'ativos_cliente' ON 'chamados'.'fk_ativo_chamado' = 'ativos_cliente'.'id_ativo'
INNER JOIN 'categorias' ON 'chamados'.'fk_categoria_chamado' = 'categorias'.'id_categoria'
INNER JOIN 'assuntos' ON 'chamados'.'fk_assunto_chamado' = 'assuntos'.'id_assunto'
WHERE 't1'.'id_usuario' = 'chamados'.'fk_usuario_responsavel_chamado'
AND NOT EXISTS (
    SELECT *,
            MAX(id_atividade) AS id_ultima_atividade 
    FROM atividades
    WHERE atividades.fk_chamado = chamados.id_chamado
    AND (atividades.status = 3 OR atividades.status = 5)
    GROUP BY fk_chamado
)
AND 't3'.'id_usuario' = 'chamados'.'fk_usuario_criador_chamado'
AND(resumo_chamado LIKE '%teste%' OR texto_chamado LIKE '%teste%' OR id_chamado LIKE '%teste%')
ORDER BY chamados.'data_criacao_chamado' ASC
LIMIT 0,15;

mysql sql modelo-relacional operador-like

asked by anonymous 27.03.2014 / 20:43

1 answer

Redeem service result http in another function Maps Public API KEY

score 3 · Accepted Answer

It is interesting to identify each element of your sentence with a prefix, to avoid ambiguities. The sentence, at the beginning, is correct. Just a few adjustments are indicated below:

SELECT *,
       t1.usuario AS nome_usuario_responsavel,
       t1.id_usuario AS id_usuario_responsavel,
       t3.usuario AS usuario_criador_chamado
FROM   chamados c
INNER JOIN usuarios t1 ON t1.id_usuario = c.fk_usuario_responsavel_chamado
INNER JOIN usuarios t3 ON t3.id_usuario = c.fk_usuario_criador_chamado
INNER JOIN clientes cl ON c.fk_cliente_chamado = cl.id_cliente
LEFT JOIN ativos_cliente ac ON c.fk_ativo_chamado = ac.id_ativo
INNER JOIN categorias cat ON c.fk_categoria_chamado = cat.id_categoria
INNER JOIN assuntos ass ON c.fk_assunto_chamado = ass.id_assunto
WHERE NOT EXISTS (
    SELECT *,
            MAX(id_atividade) AS id_ultima_atividade 
    FROM atividades
    WHERE atividades.fk_chamado = c.id_chamado
    AND (atividades.status = 3 OR atividades.status = 5)
    GROUP BY fk_chamado
)
AND (c.resumo_chamado LIKE '%teste%' OR c.texto_chamado LIKE '%teste%' OR c.id_chamado LIKE '%teste%' OR cl.nome_cliente like '%teste%')
ORDER BY c.data_criacao_chamado ASC
LIMIT 0,15;