SQL: Turn a subquery into a join: How to refer to outside table in nested join where clause?

I am trying to change my sub-query in to a join where it selects only one record in the sub-query. It seems to run the sub-query for each found record, taking over a minute to execute:

select afield1, afield2, (
    select top 1 b.field1
    from   anothertable as b
    where  b.aForeignKey = a.id
    order by field1
) as bfield1
from sometable as a

If I try to only select related records, it doesn't know how to bind a.id in the nested select.

select afield1, afield2, bfield1
from   sometable a left join (
    select top 1 id, bfield, aForeignKey 
    from   anothertable
    where  anothertable.aForeignKey = a.id
    order by bfield) b on
       b.aForeignKey = a.id
-- Results in the multi-part identifier "a.id" could not be bound

开发者_运维知识库

If I hard code values in the nested where clause, the select duration drops from 60 seconds to under five. Anyone have any suggestions on how to join the two tables while not processing every record in the inner table?

EDIT:

I ended up adding

left outer join (
    select *, row_number() over (partition by / order by) as rank) b on
    b.aforeignkey = a.id and b.rank = 1

went from ~50 seconds to 8 for 22M rows.

---

Try this:

WITH qry AS
(
    SELECT afield1, 
           afield2, 
           b.field1 AS bfield1,
           ROW_NUMBER() OVER(PARTITION BY a.id ORDER BY field1) rn
      FROM sometable a LEFT JOIN anothertable b
        ON b.aForeignKey = a.id
)
SELECT *
  FROM qry
 WHERE rn = 1

---

Try this

select afield1, 
afield2, 
bfield1 
from sometable a 
left join 
(select top 1 id, bfield, aForeignKey from  anothertable where  aForeignKey in(a.id)  order by bfield) b on b.aForeignKey = a.id