Sql Matching exact DataSets on same table

Question

I have a table that contains data such as:

Store,Product
a,         1
a,         2
b,         3
b,         2
c,         3
c,         1
d,         1
d,         2
d,         3

I am trying to write a query that gives me the Replacement Store, so since store d sells all the product that store a sells it could be a replacement for store a but store a could not be a replacement for store d since it does not have all of the product store d sells.

So the query would return the following table:

Store,Store Replacement
a,-         
b,-     
c,- 
d,a

*Note i couldn't figure out how to make a table, so a ',' represents a separation of columns. - equals a blank in the space

Answer 1

Here is a version that does work:

select sp.store, sp2.store
from (select sp.*, count(*) over (partition by store) as numproducts
      from @storeproduct sp
     ) sp join
     @storeproduct sp2
     on sp.product = sp2.product and sp.store <> sp2.store
group by sp.store, sp2.store, numproducts
having count(sp2.product) = numproducts;

Here is the SQL Fiddle.