CODEWITHSUNDEEP
mysql
Shawn
Shawn

Reputation: 15

MYSQL A complicated query select statement

Click this to view image

I have 3 products. 76,77,78. I want to return rows which have have distinct specifications_id but ALL product must have that specifications_id.

For example: In the image above, 76, 77, 78 each have specifications_id of 1. I would want to return that row.

But in the example of product_id is 76, where it has a specifications_id of 2, the other two products (77,78) do not have a specifications_id = 2.

I know how to do it using a series of loops ect, but I'm curious if there's a shorter way in mysql.

Say this table had several rows and I wanted all the products which had the same specifications_id in all 3 products.

Upvotes: 0

Views: 33

Answers (1)

isaace
isaace

Reputation: 3429

If I understood your question correctly, this should do it: 

select * from myTable where specifications_id in
( 
   select specifications_id from myTable 
   having count(specifications_id) > 2
 )

Upvotes: 1

Related Questions