LIKE query in related table

Question

I have a query that searches for TECHNICAL CALLS and in this research I do some JOINS to bring related information. I need the 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;

Answer 1

It is interesting to identify each element of your sentence with a prefix, to avoid ambiguities. The sentence, at first, is correct. Only a few adjustments left, 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;