2

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?

Improve this question
1

3 Answers 3

Reset to default
1

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] );
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

That did the job. Thank you.
0

I'm sure you'll get many better answers, but it sounds like you are wanting the reverse of the distinct clause, not sure if this will work though:

SELECT NOT DISTINCT [Product_ID] FROM TABLENAMEHERE

Improve this answer

4 Comments

That won't work, just realised. How about something like this:
SELECT ProductIDFROM Customers WHERE EmailAddress IN (SELECT EmailAddress FROM Customers GROUP BY EmailAddress HAVING COUNT(*) > 1)
Apologies, not used to enter being post!, follow this link: stackoverflow.com/questions/13146304/…, which is where I got the full answer below from: SELECT ProductID FROM Tablename WHERE ProductID IN (SELECT ProductID FROM Tablename GROUP BY ProductID HAVING COUNT(*) > 1)
And the Order By ProductID at the end. Sorry, new to the forum, should have put that all in one query for you!
0

You could sue count(distinct productID)

 select productID from my_table group by productID having count(distinct productID) = ( select count(distinct store) from my_table )
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.