Kirtash7 Kirtash7 - 3 months ago 8
SQL Question

SQL need help building a query

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?

Answer

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
;
Comments