0

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.

philipxy
  • 14,867
  • 6
  • 39
  • 83
martineno
  • 2,623
  • 17
  • 14
  • This involves "relational division". This is a faq. Although not that easy to find, because people don't distill a clear concise complete statement of what they want to do. (Like here.) Nevertheless: Before considering posting please read a textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. – philipxy May 20 '20 at 04:28
  • Your spec has a classic ambiguity associated with queries involving & almost involving relational division. You say "articles that belong to all the categories". If there are no categories, do you want all the articles, or none? The former is "articles for which, for every category, they're in it", the latter adds "... and that're in a category"--relational division. For the quoted phrasing one might think, if there are not categories then there isn't any belonging so no articles have belonged, so, none; but we don't know if that subtle difference is on purpose. Division articles discuss this. – philipxy May 20 '20 at 05:02

1 Answers1

2

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

Arun Palanisamy
  • 5,281
  • 6
  • 28
  • 53