V_RocKs V_RocKs - 4 months ago 8
SQL Question

MYSQL: Exclude a product from a result based on category when several exist

I have two tables of data. One is products and the other is a taxinomy table of categories they are in. I cannot figure out how to make an exclusion work and cover all instances in the taxonomy table.

Table Products

ID | Name
1 | Apples
2 | Oranges
3 | Potatoes
4 | Rosemary


Table Category

TaxID | ID | CategoryID
1 | 1 | 10
2 | 2 | 10
3 | 1 | 20
4 | 2 | 20
5 | 3 | 20
6 | 3 | 30
7 | 4 | 40


Now if I request this:

SELECT p.ID, Name, categoryID FROM Products p JOIN Category c ON p.ID = c.ID WHERE CategoryID != 30 AND CategoryID != 40 GROUP BY p.ID


I Get:

ID | Name | CategoryID
1 | Apples | 10
2 | Oranges | 10
3 | Potatoes | 20


What I wanted was just the Apples and the Oranges and to have Potatoes not show up because they are also included in CategoryID 30. It excludes the Rosemary, but on the Potatoes it includes it because it has another category in the taxonomy table.

Answer

Try this code should be faster:

SELECT p.ID, p.Name, c.categoryID, c2.ID 
FROM Products p JOIN Category c ON p.ID = c.ID 
LEFT JOIN Category c2 ON (c2.ID = c.ID AND c2.CategoryID IN (30,40))
WHERE c2.ID IS NULL
GROUP BY p.ID

Here is the sqlfiddle link: http://sqlfiddle.com/#!9/8d7354/12

Comments