sion griffiths sion griffiths - 22 days ago 4
MySQL Question

Select entries appearing in all results from another query

I have a single table.

This table has 2 fields, product IDs and Store IDs. The same product ID can exist with many different Store IDs.

I need to find the products (if any) that are common across all the stores.

I'm having difficulty constructing the correct query, any advice?

Answer

You can check distinct store ids count with product id. If distinct Store ids count equal to total stores that will be the product ids you want.

SELECT productID, count(DISTINCT StoreID) as stroes FROM [Table name] GROUP BY productID 
HAVING COUNT(DISTINCT StoreID) = (SELECT COUNT(DISTINCT StoreID) FROM [Table name] );
Comments