CODEWITHSUNDEEP
mysqljoin
V_RocKs
V_RocKs

Reputation: 134

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.

Upvotes: 0

Views: 92

Answers (2)

Malinga
Malinga

Reputation: 515

You can try below:

SELECT p.ID, Name, categoryID
FROM Products p JOIN Category c
    ON p.ID = c.ID
WHERE p.ID NOT IN
(
    SELECT ID
    FROM Category
    WHERE CategoryID IN (30, 40)
)
GROUP BY p.ID

Upvotes: 0

Bilas Sarker
Bilas Sarker

Reputation: 459

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

Upvotes: 1

Related Questions