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
The query didn't return with the expected results. Can you help?
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
;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments