MySQL SELECT where if stores have X, show me everything that they have
- by tnt0932
I have a MYSQL table like this:
STORES (SPID, StoreID, ProductID);
And data like this:
{ 1 | s1 | p2 }
{ 2 | s1 | p7 }
{ 3 | s1 | p8 }
{ 4 | s2 | p1 }
{ 5 | s2 | p3 }
{ 6 | s2 | p6 }
{ 7 | s2 | p9 }
{ 8 | s3 | p2 }
{ 9 | s3 | p5 }
And I would like to:
Use a MYSQL SELECT query to get results, where if a store has a certain product, then a list of all of the products at that store will be returned.
For example, if I were looking for "p2", then I would like to know that "s1" has "p2, p7, p8" and "s3" has "p2, p5".
Please let me know if you require any more information. I'm not sure what is required to solve this issue (Which is probably why I can't search and find the answer!) Thank you.