SQL Question

• marcus

  SSC Enthusiast

  Points: 103

  More actions

  January 21, 2005 at 7:12 am

  #163645

  Hi,

  I have a small SQL Question.

  The following table is given, UserID and ItemID are together unique, the type is not important.

  UserID |ItemID

  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  1|AA

  1|BB

  1|CC

  2|AA

  2|DD

  2|EE

  3|BB

  3|FF

  3|GG

  4|XX

  4|AA

  4|BB

  Now I'm looking for a list with all UserIDs which are assigned to Item AA and Item BB (to both of them!). In this example it would be UserID 1 and UserID 4.

  Result:

  UserID

  ~~~~~~

  1

  2

  Is this possible with pure SQL?

  Any help would be appreciated

  thanks,

  marcus

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  January 21, 2005 at 8:07 am

  #538520

  It would look something like this :

  Select UserId from Mytable where ItemID in ('AA','BB') GROUP BY UserID having count(*) = 2

• Greg Charles

  SSC-Forever

  Points: 45403

  More actions

  January 25, 2005 at 10:03 am

  #538995

  Or this:

  select t1.UserId from Mytable t1 join Mytable t2

  on t1.UserId = t2.UserId

  where t1.ItemId = 'AA' and t2.ItemId = 'BB'

  Greg

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  January 25, 2005 at 11:08 am

  #539015

  Greg's solution would work nicely... but if you ever need to add CC, DD, and EE to the in() clause you'd have to add 3 more inner joins.. I don't think the speed would be much slower since very few rows would get pass the first 2 joins but that would be a lot harder to maintain.

• marcus

  SSC Enthusiast

  Points: 103

  More actions

  January 26, 2005 at 2:28 am

  #539094

  thanks a lot guys, I will try both solutions (both look good), and then I will look what works best for the specific problem.

  thanks again,

  marcus