How to pass a parameter into a subquery in a join statement [closed]

I have a query that should be implemented in an MSSQL View so no local variables allowed, this query is working but it has to call a function to return a temporary table, this function is called inside a subquery, inside a left join, and I get the multi-part identifier …. can not be bound error. The query looks like this:

Select a, b, c,...., z From  tablea
left join (select * from fnGetTempTable (tablea.pkid) t1 on tablea.id = t1.id
left join ....etc

This temporary table returned by the function requires the parameter that tablea has, but I can’t get it to that level, any suggestions please?

You can use outer apply.

Note, however, that in an apply, the entire inner select is logically evaluated per row. So the results may be different from what you’d expect from a join, where rows are matched based on the on clause. (Obviously the compiler may choose other strategies, the effect will be the same.)

There is no on clause, instead use a where inside the apply.

Use cross apply to simulate an inner join, use outer apply for a left join

Select a, b, c,...., z
From  tablea
outer apply (
  select *
  from fnGetTempTable (tablea.pkid) t1
  where tablea.id = t1.id
) t1
left join ....etc