declare @Items table(ItemID int,ItemName varchar(20))
declare @ItemValues table(ItemID int,ItemValueID int)
insert @Items
select 1,'AAA'
union
select 2,'BBB'
union
select 3,'CCC'
insert @itemvalues
select 1,100
union
select 1,101
union
select 1,102
union
select 2,100
union
select 2,101
union
select 2,103
union
select 3,100
union
select 3,101
union
select 3,104
union
select 3,105
declare @BeingSearchedItemValueIDs table (ItemValueID int)
insert @BeingSearchedItemValueIDs
select 100
union
select 101
union
select 102
result should be:
ItemID ItemName
1 AAA