0

I want only those products which satisfies following condition :

Which have ((category_id = 1 OR category_id = 2) AND (category_id = 3 OR category_id = 4))

Database structure:

products

id  int(10)     
name    varchar(128)

categories

Id  int (10)
name    varchar(64)

category_products

product_id  int(10)
category_id int(10)

Data:

My product details are:

Products:
id  name
1   P1
2   P2
3   P3
4   P4

My category details are:

Categories:
Id  name
1   C1
2   C2
3   C3
4   C4

My category-product mapping details are:

category_products:
product_id   category_id
1            1
1            3
1            4
2            2
3            1
3            2
3            3
3            4
4            1
4            4   



Final Output should be:
    id
    1
    3
    4
Nishit Modi
  • 175
  • 1
  • 18

3 Answers3

1
SELECT DISTINCT p.id, p.name FROM products AS p
INNER JOIN category_products AS c1 ON c1.product_id=p.id
INNER JOIN category_products AS c2 ON c2.product_id=p.id
WHERE c1.category_id IN (1,2) AND c2.category_id IN (3,4)

It wouldn't work to use

WHERE c.category_id IN (1,2) AND c.category_id IN (3,4)

because a WHERE clause tests just one row at a time, and there's no way one category_id can have two values on a single row. So the self-join handles that, by matching more than one row and referencing them by correlation names.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

Try this:-

select * 
from products 
where id in (select distinct(product_id) 
             from category_products 
             where (category_id =3 0R category_id=4) 
               AND (category_id =1 OR category_id=2))
Nithesh Narayanan
  • 11,481
  • 34
  • 98
  • 138
abhinsit
  • 3,214
  • 4
  • 21
  • 26
0
SELECT p.product_id AS id FROM product_categories AS p
WHERE (p.category_id=1 OR p.category_id=2) AND (p.category_id=3 OR p.category_id=4) GROUP BY p.category_id

Because you don't need the name of the product you don't need an INNER JOIN. GROUP BY and DISTINCT should both work.

smcjones
  • 5,490
  • 1
  • 23
  • 39