CODEWITHSUNDEEP
mysqlsqlsubqueryleft-joininner-join
Marques
Marques

Reputation: 399

Select status of last activity of each ticket and use the status within WHERE

I Have 2 tables, chamados(tickets) and atividades(activities) (1 for N)

Basically I need to select all 'chamados(tickets)' where last 'atividade(activity)' status is not 3 or 5;

I've tried within the SELECT and LEFT JOIN, nothing, I not got it yet.

My query is:

SELECT  *
FROM (`chamados`)
ORDER BY `data_criacao_chamado` ASC 
LIMIT 0,20;

I think that the query should seem something like:

SELECT  *,
    (
        SELECT status FROM atividades WHERE atividade.fk_chamado = chamado.id_chamado ORDER BY id_atividade DESC LIMIT 1 
    ) as status_item
FROM (`chamados`)
WHERE (status_item != 3 AND status_item != 5)
ORDER BY `data_criacao_chamado` ASC 
LIMIT 0,20;

Upvotes: 0

Views: 104

Answers (1)

oceansize
oceansize

Reputation: 731

SELECT C.data_criacao_chamado FROM atividades A
JOIN 
    (SELECT fk_chamado, MAX(id_atividade) AS latest_atividade FROM atividades GROUP BY fk_chamado) LATEST_ATIVIDADES    
    ON A.fk_chamado = LATEST_ATIVIDADES.fk_chamado AND A.id_atividade = LATEST_ATIVIDADES.latest_atividade AND A.status_item NOT IN (3,5)
JOIN 
    chamados C ON C.id_chamado = A.fk_chamado

Upvotes: 1

Related Questions