3

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

6

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' ;
2

UNPIVOT the result of the query.

select  * 
from    Extras 
where   extra = 'Potatoes' 
    and foodId in 
        (
            select  id

            from   (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'
                    ) f unpivot (id for col in (chickenId,beefId,fishId)) u
        )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.