CODEWITHSUNDEEP
mysql
ScottyD
ScottyD

Reputation: 25

In Mysql, select distinct values that occur with each instance of another value

I have a table with supplier id's (sid), part id's (pid), and job id's (jid). Each have many instances, but I need to find suppliers that occur with each part.

Here is an example table:

+------+------+------+------+
| sid  | pid  | jid  | qty  |
+------+------+------+------+
| S1   | P1   | J1   |  200 |
| S1   | P1   | J4   |  700 |
| S2   | P3   | J1   |  400 |
| S2   | P3   | J2   |  200 |
| S2   | P3   | J3   |  200 |
| S2   | P3   | J4   |  500 |
| S2   | P3   | J5   |  600 |
| S2   | P3   | J6   |  400 |
| S2   | P3   | J7   |  800 |
| S3   | P3   | J1   |  200 |
| S3   | P4   | J2   |  500 |
| S4   | P6   | J3   |  300 |
| S4   | P6   | J7   |  300 |
| S5   | P2   | J2   |  200 |
| S5   | P2   | J4   |  100 |
| S5   | P5   | J5   |  500 |
| S5   | P5   | J7   |  100 |
| S5   | P6   | J2   |  200 |
| S5   | P1   | J4   |  100 |
| S5   | P3   | J4   |  200 |
| S5   | P4   | J4   |  800 |
| S5   | P5   | J4   |  400 |
| S5   | P6   | J4   |  500 |
+------+------+------+------+

So this should return

+------+
| sid  | 
+------+
| S5   |
+------+

Since S5 is the only supplier id that occurs with all 6 of the parts (pid). I figure I would use subselects, but have no idea how to go about doing this.

Upvotes: 1

Views: 51

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

Try this:

SELECT sid
FROM mytable
GROUP BY sid
HAVING COUNT(DISTINCT pid) = (SELECT COUNT(DISTINCT pid) FROM mytable)

The subquery:

SELECT COUNT(DISTINCT pid) FROM mytable

returns the number of distinct part numbers existing in your table. The main query groups by sid and its HAVING clause selects only sid groups that contain all existing part numbers.

Demo here

Upvotes: 1

Related Questions