MYSQL A complicated query select statement

Question

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.

I adjusted the text for the link. I apologize for the confusion. This is my first question on this site. — Shawn
– Shawn, Commented Dec 11, 2017 at 20:31

isaace · Accepted Answer · 2017-12-11 20:39:40Z

1

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
 )

answered Dec 11, 2017 at 20:19

isaace

3,4291 gold badge11 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

what do you get when you run the inner query itself? try "having count(specifications_id) > 2 " instead.

How come = 3 doesn't?

I am not sure. do you have that id more than 3 times?

Nope. If you click link at top., it should show an image of the table I'm using.

interesting. I don't know.

|