Selecting row value only if other rows are equal

Jeppe 0
Viewed 25

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
stored-procedures mysql sql
1 Answers

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)

To do this within a stored procedure:

CREATE PROCEDURE getSourceWithAllIds() 
   BEGIN
   SELECT source FROM
   table_name
   GROUP BY source
   HAVING COUNT(DISTINCT id)=(SELECT COUNT(DISTINCT id) FROM table_name);
   END

The idea is to select the count of distinct id values for each source, which is done by COUNT(DISTINCT id)... GROUP BY source, then match this count with the distinct count of all id values existed in the table; HAVING COUNT(DISTINCT id)=(SELECT COUNT(DISTINCT id) FROM table_name).

If the two counts are equal, then the source have all the distinct ids existed in the table.

i.e. All distinct ids in the table are (1, 2, 3) count = 3, and distinct ids for a source =10 are (1, 2, 3) count=3. For source = 30, the distinct ids are (1, 2) count=2 so it will not be returned by the query (2<>3).

See a demo.

userGpjR0
Related