Querying records

[brianlange]

I have a articles table and a categories table and a join table. The categories are not in a hierarchical structure.

My issue is querying for articles that belong to two or more categories. The query must check that the article has a corresponding record in the join table for each category.  What's the best way to accomplish this? I thought of using two queries and then performing a merge but this doesn't seem like the best approach.

 

-Brian

[kickstart]

Hi

 

To get all the articles and all the categories they belong to you would use something like this (column names guessed)

 

SELECT *
FROM articles
INNER JOIN jointable ON articles.article_id = jointable.article_id
INNER JOIN categories ON jointable.category_id = categories.category_id

 

If you wanted ONLY those where the article was in 2 or more categories then the following would give you a list of the articles and the number of categories they were in (again column names guessed)

 

SELECT articles.article_id, articles.article_name, COUNT(categories.category_id) AS CategoryCount
FROM articles
INNER JOIN jointable ON articles.article_id = jointable.article_id
INNER JOIN categories ON jointable.category_id = categories.category_id
GROUP BY articles.article_id, articles.article_name
HAVING CategoryCount >= 2

 

(strictly speaking you don't have to JOIN with the category table here as the link table has the count of categories).

 

If you wanted the categories as well then you could use the following to just give you a list of all the categories for each article which has more than 2 categories:-

 

SELECT articles.article_id, articles.article_name, COUNT(categories.category_id) AS CategoryCount, GROUP_CONCAT(categories.category_name) AS ArticlesCategories
FROM articles
INNER JOIN jointable ON articles.article_id = jointable.article_id
INNER JOIN categories ON jointable.category_id = categories.category_id
GROUP BY articles.article_id, articles.article_name
HAVING CategoryCount >= 2

 

If you want a row for each article and each of its categories then something like this:-

 

SELECT *
FROM articles
INNER JOIN jointable ON articles.article_id = jointable.article_id
INNER JOIN categories ON jointable.category_id = categories.category_id
INNER JOIN (SELECT articles.article_id, COUNT(categories.category_id) AS CategoryCount
FROM articles
INNER JOIN jointable ON articles.article_id = jointable.article_id
INNER JOIN categories ON jointable.category_id = categories.category_id
GROUP BY articles.article_id, articles.article_name
HAVING CategoryCount >= 2) z ON articles.article_id = z.article_id

 

Hope that gives you some ideas (not tested so excuse any typos).

 

All the best

 

Keith