CODEWITHSUNDEEP
mysqlsql
S.M_Emamian
S.M_Emamian

Reputation: 17383

How to find all rows that have all another column values

I have two tables like these:

Tb1
------------
col
1
2
3

Tb2
-----------
id name Tb1_col
1   a    1
2   a    2
3   b    1
4   a    3

Now I want to fetch all name values from Tb2 that have all col values from Tb1. For this example I just expect a.

Upvotes: 0

Views: 37

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

If tb1_col is defined as a foreign key (meaning that all values are valid references), then Nick's answer can be simplified:

SELECT Tb2.name
FROM Tb2
GROUP BY Tb2.name
HAVING COUNT(DISTINCT Tb2.Tb1_col) = (SELECT COUNT(*) FROM Tb1)

If tb1_col never duplicates for a given name, then using:

HAVING COUNT(*) = (SELECT COUNT(*) FROM Tb1)

should be even more efficient.

Upvotes: 0

Anatoliy R
Anatoliy R

Reputation: 1789

Not very elegant solution, but....

SELECT distinct name from Tb2 as t1
WHERE NOT EXISTS (SELECT t2.col, t3.id
                  FROM Tb1 as t2 LEFT JOIN Tb2 as t3 ON t3.Tb1_col = t2.col
                                                     AND t3.name = t1.name
                  WHERE t3.id IS NULL);

Upvotes: 0

Nick
Nick

Reputation: 147146

One way to do this is to JOIN Tb2 to Tb1 on Tb1_col and check that the number of distinct values for a given name is the same as the number of rows in Tb1:

SELECT Tb2.name
FROM Tb2
JOIN Tb1 ON Tb1.col = Tb2.Tb1_col
GROUP BY Tb2.name
HAVING COUNT(DISTINCT Tb2.Tb1_col) = (SELECT COUNT(*) FROM Tb1)

Output

a

Demo on dbfiddle

Upvotes: 1

Related Questions