MySQL Choose Where From Many To Many

Question

I am having problems with SQL query. My diagram describes the relationship between many articles in the articles table and categories of the categories table — with the staging table article_category , which has the id , article_id and category_id fields.

I want to select all articles in which there are only categories with id 1 and 2 . Unfortunately, this query will also select any articles that have these categories in addition to others.

For example, this is an example of output from SQL ( with categories shown for descriptive purposes ). You can see that although the query selected an article with ID 10 , she also selected an article with ID 11 , despite having one additional category.

 +-------+------------+ | id | categories | +-------+------------+ | 10 | 1,2 | | 11 | 1,2,3 | +-------+------------+ 

This is the result that I want to achieve from the selection of articles with categories 1 and 2 .

 +-------+------------+ | id | categories | +-------+------------+ | 10 | 1,2 | +-------+------------+ 

Similarly, this is the result that I want to achieve from the selection of articles with categories 1 , 2 and 3 .

 +-------+------------+ | id | categories | +-------+------------+ | 11 | 1,2,3 | +-------+------------+ 

This is the SQL I wrote. What am I missing to achieve the above?

 SELECT articles.id FROM articles WHERE EXISTS ( SELECT 1 FROM article_category WHERE articles.id = article_id AND category_id IN (1,2) GROUP BY article_id ) 

Many thanks!

Answer 1

Assuming you want more than just an article id:

 SELECT a.id ,a.other_stuff FROM articles a JOIN article_category ac ON ac.article_id = a.id GROUP BY a.id HAVING GROUP_CONCAT(DISTINCT ac.category_id ORDER BY ac.category_id SEPARATOR ',') = '1,2' 

If all you need is an article id, try the following:

 SELECT article_id FROM article_category GROUP BY article_id HAVING GROUP_CONCAT(DISTINCT category_id ORDER BY category_id SEPARATOR ',') = '1,2' 

Take a look at the action at http://sqlfiddle.com/#!2/9d213/4

It should also be added that the advantage of this approach is that it can support validation of any number of categories without the need to modify the query. Just make a '1,2' string variable and change what is passed into the request. Thus, you can easily find articles with categories 1, 2 and 7 by passing the string "1,2,7". No additional associations are required.

Answer 2

You can leave join category_id on category.id and then GROUP_CONCAT to get all categories as you wrote in the explanation (1st table), and not using HAVING match with any set you like ('1,2' from example)

also with this approach you can easily make this request dynamic with php or any other language

 SELECT articles.id FROM articles WHERE EXISTS ( SELECT GROUP_CONCAT(c.id) AS grp FROM article_category LEFT OUTER JOIN categories AS c ON c.id = article_category.category_id WHERE articles.id = article_id GROUP BY article_id HAVING grp = '1,2' ) 

Answer 3

Please use the below request. You can do thing with a simple request.

  SELECT a.id, a.name FROM articles a, categories c, articles_categories ac WHERE a.id = ac.article_id AND c.id = ac.category_id AND c.id = 1 OR c.id = 2; 

NOTE If you have a lot for many. The relationship between two tables. Remove the ID from the article_category table and create a composite primary key. Use article_id and category_id.

Thanks.

Answer 4

I like to access these queries with group by and having . Here is an example:

 select ac.article_id from article_category ac group by ac.article_id having sum(case when category_id = 1 then 1 else 0 end) > 0 and sum(case when category_id = 1 then 2 else 0 end) > 0; 

Each condition in the having checks for one of the categories.

I find this approach to be the most flexible to answer many different set-in-sets problems.

EDIT:

A small change to the above can be more easily generated:

 having sum(category_id in (1, 2, 3)) = count(*) and count(*) = 3 

This will work if there are no duplicates in the data. You need to update 3 as the number of items in the in list.

Answer 5

Maybe something like:

 select distinct article_id from article_cathegory where category_id in (1,2) minus select distinct article_id from article_cathegory where category_id not in (1,2) 

Answer 6

It seems like a simple solution to this could be the following:

 SELECT ac.article_id , SUM(ac.category_id IN (1, 2)) AS nb_categories , COUNT(ac.category_id) AS nb_all_categories FROM article_categories ac GROUP BY ac.article_id HAVING nb_categories=2 AND nb_all_categories=2 

Here I am counting how many required categories we have, and also counting how many categories we have. We need exactly 2 categories, therefore both required and general should be equal to 2.

This is quite flexible and to add more categories just change the list of categories and numbers in the HAVING statement.

Answer 7

 SELECT articles.id FROM articles INNER JOIN articles_category ac ON articles.id = ac.article_id WHERE articles.id IN ( SELECT ac1.article_id FROM article_category ac1 WHERE ac1.category_id = 1; ) AND ac.article_id = 2; AND articles.id NOT IN ( SELECT ac2.article_id FROM article_category ac2 WHERE ac2.category_id NOT IN (1, 2) ) 

Far from the most beautiful that I wrote.

Basically, it restricts first to an identifier that has a category 1 identifier, then it guarantees that the entries also have category 2, finally it guarantees that it has no other categories

Answer 8

to help without changing your request, I think there is an error in the logic. You do not need articles in which the category 1,2 exists. You need articles in which there are no categories other than 1 and 2. Thank you and welcome

Answer 9

In SQL Server, I would do this with INTERSECT and EXCEPT. In MySQL, try the following:

 SELECT DISTINCT article_id FROM article_category WHERE category_id=1 AND article_id IN (SELECT article_id FROM article_category WHERE category_id=2) AND article_id NOT IN (SELECT article_id FROM article_category WHERE category_id NOT IN (1,2)) 

Answer 10

Use this SQL query.

 SELECT articles.id FROM articles WHERE articles.id in ( SELECT * FROM article_category,articles WHERE article_category.articles.id = articles.article_id AND article_category.category_id IN (1,2) )