SQL Server – Using IN with Multiple Rows

sql serversql-server-2008

So I got a query with a whole bunch of joins and that returns ONE row, like this (just an example):

select t1.chickenId, t2.beefId, t3.fishId from Food f
join t1 on t1.id = f.id
join t2 on t2.id = f.id
join t3 on t3.id = f.id
where f = 'something'

A result would be like this for example: 

chickenId | beefId | fishId
5          58         14

Now the part I don't know how to do… The reason for using the above query is because I want to use the result to find something in another table.

I have another table named "Extras" and I want the rows from that table that have the ids from any of the columns of the previous query. So if the id 5, 58 or 14 is in Extras table, I want those rows.

This following query is incorrect but I believe it can be used to demonstrate what I'm trying to achieve:

select * from Extras where extra = 'Potatoes' and foodId in (select t1.chickenId, t2.beefId, t3.fishId from Food f
    join t1 on t1.id = f.id
    join t2 on t2.id = f.id
    join t3 on t3.id = f.id
    where f = 'something')

Any idea how I can achieve this? I'm using Microsofts SQL Server 2008

Best Answer

Your IN subquery has 3 columns, so it can't be compared with the one value of Extras.foodId.

You could use EXISTS instead and move the IN condition inside, making it a correlated subquery:

select e.* 
from Extras as e
where e.extra = 'Potatoes'
  and exists
      ( select *
        from Food as f
          join t1 on t1.id = f.id
          join t2 on t2.id = f.id
          join t3 on t3.id = f.id
        where f.f = 'something' 
          and e.foodId in (t1.chickenId, t2.beefId, t3.fishId)
      ) ;

Another possibility would be to keep the IN syntax you have and convert the 3 columns into one (and each row into 3) with CROSS APPLY:

select e.* 
from Extras as e 
where e.extra = 'Potatoes' 
  and e.foodId in 
      ( select v.foodId
        from Food as f
          join t1 on t1.id = f.id
          join t2 on t2.id = f.id
          join t3 on t3.id = f.id
          cross apply
            ( select t1.chickenId union
              select t2.beefId    union
              select t3.fishId
            ) as v (foodId) 
        where f.f = 'something' 
      ) ;

If the first query you provide is guaranteed to return one row always, you could also use a simple join without any subqueries:

select e.* 
from Food as f
  join t1 on t1.id = f.id
  join t2 on t2.id = f.id
  join t3 on t3.id = f.id
  join Extras as e 
    on e.foodId in (t1.chickenId, t2.beefId, t3.fishId)
where e.extra = 'Potatoes'
  and f.f = 'something' ;
Related Question