A multi-part identifier cannot be associated in the left outer join with the table value function

Question

I am writing this code

SELECT tblprsn.prsnid, tblprsn.name FROM tblprsn LEFT OUTER JOIN ( SELECT tblrtm.rtmid FROM dbo.getrtmn(tblprsn.prsnid) as getrtmn_1 ) AS tblgetrtmn ON tblprsn.prsnid = tblgetrtmn.rtmid 

dbo.getrtmn is a table-value function and has a uniqueidentifier field named rtmid. prsnid - unique identifier

When I ran this SQL query query, follow these steps:

The multi-part identifier "tblprsn.prsnid" cannot be associated.

Answer 1

This is your request:

 SELECT tblprsn.prsnid, tblprsn.name FROM tblprsn LEFT OUTER JOIN (SELECT tblrtm.rtmid FROM dbo.getrtmn(tblprsn.prsnid) as getrtmn_1 ) AS tblgetrtmn ON tblprsn.prsnid = tblgetrtmn.rtmid 

You are referencing the first table in the subquery. It is forbidden. First, I don’t think a subquery is necessary. You select only the first table and do a left outer join to save all records. The only effect of the subquery would be line multiplication. So, as you wrote it, this query should do almost the same thing (except for duplicates):

 SELECT tblprsn.prsnid, tblprsn.name FROM tblprsn; 

In the original query, you can do what you want using apply rather than join :

 SELECT tblprsn.prsnid, tblprsn.name FROM tblprsn cross apply dbo.getrtmn(tblprsn.prsnid) tblgetrtmn where tblprsn.prsnid = tblgetrtmn.rtmid 

Answer 2

instead of LEFT OUTER JOIN, use OUTER APPLY with the appropriate subquery. The OUTER APPLY clause returns all rows on the left side of the table, whether they return any rows in the table, a value function or not, and thus are similar to LEFT OUTER JOIN

 SELECT t.prsnid, t.name, getrtmn_1.* FROM tblprsn t OUTER APPLY ( SELECT rtmid FROM dbo.getrtmn(t.prsnid) AS tblgetrtmn WHERE t.prsnid = tblgetrtmn.rtmid ) as getrtmn_1 

SQLFiddle Demo