Query question

[jscriptor09]

Hi

I have a situation where the data looks as follows:

store1 item1
store1 item2
store1 item3

store2 item1
store2 item3
store2 item4
store2 item5

store3 item2
store3 item3
store3 item4
store3 item6

store4 item1


store4 item8
store4 item7
store4 item9

I need to write a SQL that:
look for the store that carries item 2 and return all items in the stores found. In this example, the return values should be

store1 item1
store1 item2
store1 item3

store3 item2
store3 item3
store3 item4
store3 item6

Note that all items in stores 1 and 3 are returned; not just the 2 rows where Item 2 was found.

I appreciate any help constructing the SQL for this

Thanks
Mike

[Dal Jeanis]

There's lots of ways, but something like this would work:

SELECT T1.StoreName, T1.ItemName
FROM MyTable AS T1
WHERE T1.StoreName IN 
(SELECT T2.StoreName 
FROM MyTable AS T2
WHERE T2.ItemName = "Item 2");

or this:

SELECT T1.StoreName, T1.ItemName
FROM MyTable AS T1, MyTable AS T2
WHERE ((T1.StoreName = T2.StoreName)
AND (T2.ItemName = "Item 2"))
GROUP BY T1.StoreName, T1.ItemName;

[jscriptor09]

Hi Dal

Thanks a lot. This works.

Mike