what is the best way to find an itemID from a given itemvalueIDs

• michaelwang-772176

  Grasshopper

  Points: 24

  More actions

  September 25, 2009 at 3:20 pm

  #138344

  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

• Jan Van der Eecken

  SSCrazy Eights

  Points: 9013

  More actions

  September 25, 2009 at 3:45 pm

  #1058679

  Hi Michael,

  Don't want to give away the answer, because the whole point of these forums is to teach people how to teach themselves. Just a hint, a simple LEFT JOIN from @BeingSearchedItemValueIDs to @itemvalues on ItemValueId with a DISTINCT thrown in will do the trick. Look up the correct syntax in BOL.

  Jan

  --------------------------------------------------------------------------
  A little knowledge is a dangerous thing (Alexander Pope)
  In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]