CODEWITHSUNDEEP
mysqlsql
Ravinder Reddy
Ravinder Reddy

Reputation: 3879

Get categories which do not have any product from a particular category

I am trying to write a sql query to get the categories which does not have any of the products from particular category. Let say I have a,b,c,d,e categories and each category have some products. Now I need to get all the categories which done not include products of category a.

Categories table:

id  name
1   A
2   B
3   C
4   D
5   E

category_products table:

product_id   category_id
1            1
1            2
2            3
2            1
4            3
3            2
3            4
3            5
4            5

Query I used is below which gives B,C,D,E (not as expected)

SELECT DISTINCT c.name FROM category_products AS p
LEFT JOIN categories AS c ON c.id = p.category_id
WHERE p.product_id NOT IN (SELECT DISTINCT product_id FROM category_products where category_id = 1)
ORDER BY c.name

But I need results to be categories D,E which don't have any products from category A.

Upvotes: 0

Views: 1373

Answers (3)

Darshan Mehta
Darshan Mehta

Reputation: 30819

You need to do one more inner query, e.g.:

SELECT name
FROM categories 
where id NOT IN (
    SELECT DISTINCT category_id 
    FROM category_products WHERE 
    product_id IN (
        SELECT product_id FROM category_products WHERE category_id = 1
    )
);

This would return D and E.

Here's the SQL Fiddle.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I would be inclined to do this using group by and having:

select pc.product_id
from category_products pc join
     categories c
     on c.id = pc.category_id
group by pc.product_id
having sum(c.name = 'A') = 0;

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You should use inner join 

  select distinct  t2.name
  from category_products t1 
  inner join Categories t2 on t2.id = t1.category_id
  where t1.product_id not in 
   (select p.product_id
   from category_products p
   inner join Categories c on c.id = p.category_id
   where c.name ='A')

Upvotes: 0

Related Questions