CODEWITHSUNDEEP
sqlsql-serverjoin
Chris
Chris

Reputation: 23

Complex SQL Join on sub query with top 1 records

I'm trying to write a query that looks something like below.

select t1.t1c1, t1.t1c2, t2.t2c3, t2.t2c4
from table1 t1 
left outer join (select top 1 t2c1, t2c2, t2c3, t2c4 from table2 
                 where t2c5 in (select t3c1 from table3 t3
                                where **t3c2 = t1.t1c2 and t3c3 = t1.t1c3**) t2
           on t1.t1c1 = t2.t2c1 and t1.t1c2 = t2.t2c2

What SQL Server does not allow is the highlighted text above - i.e. referencing the table1's columns in the table3 sub query. Is there a way to achieve this? I understand that this might not be the most optimal way, is there any other way to achieve this?

Upvotes: 2

Views: 59

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You seem to exactly want outer apply. I think it would look like this:

select t1.t1c1, t1.t1c2, t2.t2c3, t2.t2c4
from table1 t1 outer apply
     (select top 1 t2c1, t2c2, t2c3, t2c4
      from table2 
      where t2c5 in (select t3c1
                     from table3 t3
                     where t3c2 = t1.t1c2 and t3c3 = t1.t1c3
                    ) and
            t1.t1c1 = t2.t2c1 and t1.t1c2 = t2.t2c2
     ) t2;

APPLY is a lot like using a correlated subquery, except it goes in the FROM clause and can return multiple columns and multiple rows.

Note: You should be using ORDER BY when you use TOP.

Upvotes: 3

Related Questions