I need your help building a query.
I have two tables:
The first table (table1) gives me the historical status , all the status that my product passed and the second table(table2) tells me the status at this moment for my product.
the id columns are the same for both tables like the status column.
I want to build a query that tells me the amount of my products that are with the status D,E and F in my table 2 but on my table 1 didn't passed for the status C, like going to status B to status D,E or F without passing to C.
I tried running this query:
select count(id), status
from table1 e
where status not in (C) EXISTS (SELECT *
FROM table2 c
WHERE e.id = c.id
AND status IN (D,E,F))
group by status
As the other responders noted, you have some syntax errors. Basically, you're just missing a few words.
select count(id) , status from table1 t1 where status not in ('C') *and* EXISTS ( SELECT * FROM table2 t2 WHERE t2.id = t1.id and status in ('D','E','F') ) group by status ;
Alternatively, you could try solving it this way. Full disclosure - this is probably not as efficient (see In vs Exists).
select count(id) , status from table1 where id not in ( select id from table1 where status not in ('C') union select id from table2 where status in ('D','E','F') ) group by status ;