Writing a query to get all records in a group based on an item in the group

Question

Sorry for the unhelpful title, but hopefully I can explain this well enough.

Lets say I have three tables,

Item
  ItemKey
  ItemName

Group
  GroupKey

GroupItem
  GroupKey
  ItemKey

Given an ItemKey, how would I get all records from the item table that belong to a group containing the item key?

So if I have

Item
   1    ItemA
   2    ItemB
   3    ItemC

Group
   1
   2
   3

GroupItem
   1     1
   1     2
   2     1
   2     2
   3     2
   3     3

Passing in 1 will return ItemA and ItemB, but not ItemC since there is no group with both ItemA and ItemC in it.

Answer 1

select distinct ItemName
from Item I
inner join GroupItem GI
    on I.ItemKey = GI.ItemKey
where GI.GroupKey in
    (select GroupKey
    from GroupItem
    where ItemKey = @Param)
-- @Param is the input parameter. Change for you
-- DBMS

or:

select distinct ItemName
from GroupItem GI_MemberOf
inner join GroupItem GI_Map
    ON GI_Map.GroupKey = GI_MemberOf.GroupKey
inner join Item I
    on I.ItemKey = GI_Map.ItemKey
where GI_MemberOf.ItemKey = @Param
-- @Param is the input parameter. Change for you
-- DBMS

Answer 2

You could use a sub-query.

SELECT DISTINCT i.ItemName
FROM GroupItem gi JOIN
  Item i ON gi.ItemKey = i.ItemKey
WHERE gi.GroupKey IN (
  SELECT DISTINCT GroupKey FROM GroupItem WHERE ItemKey = @Param
)