Find only entries that belong to all categories

Question

In PostgreSQL I have 3 tables:

1. Table of articles
2. Table of categories
3. Table that maps article to categories

I want to be able to find articles that belong to all the categories, without having to know which categories exist.

A concrete example:

articles      categories        
| id    |     | id    | name  | 
+-------+     +-------+ ------+ 
| 1     |     | 1     | cat 1 | 
| 2     |     | 2     | cat 2 | 
| 3     |     | 3     | cat 3 | 


category map       
| article_id | category_id  |
+------------+--------------+
| 1          | 1            |
| 1          | 2            |
| 1          | 3            |
| 2          | 1            |
| 2          | 2            |

In this case the article that matches the criteria is only the article 1. The article 2 doesn't match because it belong only to categories 1 and 2. Article 3 doesn't match because it doesn't belong to any categories.

If I then add another category to the list of categories, no articles would match. So the query would depend on the list of categories in the categories table.

I am not certain how to approach it in a general way.

I thought about using a cross join, but it's the general nature of the categories table that's giving me trouble.

Answer 1

You can use the below query.

SELECT ARTICLE_ID FROM CATEGORY_MAP
WHERE CATEGORY_ID IN (SELECT ID FROM CATEGORIES)
GROUP BY ARTICLE_ID
HAVING COUNT(DISTINCT CATEGORY_ID) = (SELECT COUNT(*) FROM CATEGORIES);

As you mentioned, this will take all the categories from categories table and check in Category_map whether all are available and then it will give only the article_id with all categories.

Check Demo Here