I have a table “stats” that consists 3 ids.
IDs: id_seller, id_part and id_proj
From this table, I want to find projects (id_proj) which buy specific parts (id_part) that is avaiable only from one seller.
In other words: Find id_proj, which buy id_parts, which are avaiable only from one seller (seller S5 is the only seller that sells P2).
So, In this example id_part (P2) is the only part id, that is specific and it is selling just to id_seller (S5).
The return should be: J2, J4
I ve tried with something like this:
SELECT DISTINCT s.id_proj FROM stats s WHERE NOT id_part IN ( SELECT s2.id_part FROM stats s2 WHERE s2.id_seller = 'S5');
Advertisement
Answer
select distinct id_proj from stats where id_part in (SELECT s.id_part FROM stats s WHERE s.id_seller = 'S5' and id_part not in (select id_part from stats where id_seller <> 'S5') --**this take only part from seller S5** )
if this is a homework though you should really understand the concept of IN and NOT IN like in this thread, EXIST and NOT EXIST.