I have a table with an id column and a source column. I want to return only the source values that all ids share.
E.g. in the table below id 1,2,3 all share 10 and 20, but id 3 is missing the source value 30, so 30 is not valid and I want to return 10 and 20.
I'm using MySQL and want to put this in a stored procedure.
How do I do this?
| id | source |
|---|---|
| 1 | 10 |
| 1 | 20 |
| 1 | 30 |
| 2 | 10 |
| 2 | 20 |
| 2 | 30 |
| 3 | 10 |
| 3 | 20 |
CodePudding user response：
You may use COUNT(DISTINCT) function as the following:
SELECT source FROM
table_name
GROUP BY source
HAVING COUNT(DISTINCT id)=(SELECT COUNT(DISTINCT id) FROM table_name)
See a demo.