Values linked with all values of another column

from the Artful Common Queries page

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


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:


FROM keywords




| txtID | N |


|     2 | 2 |


Return to the Artful Common Queries page