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

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');

CodePudding user response：

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.

CodePudding user response：

Use Group By with Having . Group by id_part and select pieces that have a seller.

Then join the result to the main table to get the information you need.

SELECT s2.*
FROM
    (SELECT id_part,max(id_seller) AS id_seller
    FROM stats
    GROUP BY id_part
    HAVING COUNT(DISTINCT id_seller) = 1) s1
JOIN stats s2 ON s1.id_part = s2.id_part AND s1.id_seller = s2.id_seller