Skip to content
Advertisement

Find a value from table that is only unique to one id

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).

enter image description here

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.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement