CODEWITHSUNDEEP
mysql
Joe
Joe

Reputation: 178

How to get list of product having a specific property id's

I have a table having columns 'product_id' and 'property_id'. Some products having both property 20 & 21, some of them only have 20 or 21.

product_id   property_id
1            20
1            21
2            20
3            21
3            20
4            21

I need to get list of product having both property_id 20 and 21. In here I needed the product list of 1 & 3 like below,

product_id
1
3

Upvotes: 1

Views: 76

Answers (4)

Nikhil Batra
Nikhil Batra

Reputation: 3148

Make use of IN to check multiple values.

Select product_id 
from products 
where property_id in(20,21) 
group by product_id 
having count(property_id) >=2

Upvotes: 0

Jens
Jens

Reputation: 69440

This should gives you the correct result.

select product_id 
from `xy` 
where property_id in (20,21) 
group by product_id
having count( distinct property_id)=2

The the sqlfiddle

Upvotes: 1

Raging Bull
Raging Bull

Reputation: 18737

Use IN and COUNT(distinct property_id):

SELECT product_id
FROM TableName
WHERE property_id IN (20,21)
GROUP BY product_id 
HAVING COUNT(distinct property_id) = 2

Explanation:

IN checks if property_id has values either 20 or 21.

COUNT (distinct property_id) = 2 verifies having two different values of property_id. So, when product 2 has 2 same values in property_id like (20,20), it will not be selected.

Result:

product_id
----------
1
3

Sample result in SQL Fiddle.

Upvotes: 1

Mind Stand
Mind Stand

Reputation: 1

SELECT `product_id` FROM TableName
WHERE `property_id` IN (20,21)
GROUP BY `product_id`
HAVING COUNT(*) = 2

Upvotes: -1

Related Questions