Select entries appearing in all results from another query

Question

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?

See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Nov 11 '16 at 15:49

Chandana Kumara · Accepted Answer · 2016-11-11T15:56:45.380

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] );

edited Nov 11 '16 at 15:56

answered Nov 11 '16 at 15:51

Chandana Kumara

2,485
1
22
25

1

That did the job. Thank you. – sion griffiths Nov 11 '16 at 16:06

score 0 · Answer 2 · answered Nov 11 '16 at 15:50

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

answered Nov 11 '16 at 15:50

Craig Jones

41
7

That won't work, just realised. How about something like this: – Craig Jones Nov 11 '16 at 15:52
SELECT ProductIDFROM Customers WHERE EmailAddress IN (SELECT EmailAddress FROM Customers GROUP BY EmailAddress HAVING COUNT(*) > 1) – Craig Jones Nov 11 '16 at 15:52
Apologies, not used to enter being post!, follow this link: http://stackoverflow.com/questions/13146304/how-to-select-every-row-where-column-value-is-not-distinct, 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) – Craig Jones Nov 11 '16 at 15:54
And the Order By ProductID at the end. Sorry, new to the forum, should have put that all in one query for you! – Craig Jones Nov 11 '16 at 15:59

score 0 · Answer 3 · answered Nov 11 '16 at 15:50

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 )

answered Nov 11 '16 at 15:50

ScaisEdge

131,976
10
91
107

Select entries appearing in all results from another query

3 Answers3