Values linked with all values of another column

You have a table in which each row references one text and one keyword in the text ...



DROP TABLE IF EXISTS keywords;

CREATE TABLE keywords (txtID int, keyword char(8));

INSERT INTO keywords VALUES(1 , 'foo'),(2 , 'bar'),(1 , 'foo'),(2 , 'foo');

... and you want a list of texts which include every keyword.

You might think you have to join and match. You don't. All you need to do is count the distinct keywords which occur for each text, then for each text compare that number with the entire list of distinct keywords:



SELECT txtID, COUNT(DISTINCT keyword) AS N

FROM keywords

GROUP BY txtID

HAVING N = (SELECT COUNT(DISTINCT keyword) FROM keywords);

+-------+---+

| txtID | N |

+-------+---+

|     2 | 2 |

+-------+---+