CODEWITHSUNDEEP
sqlsql-server-2005table-valued-parameters
Phoenix
Phoenix

Reputation: 129

The multi-part identifier could not be bound in left outer join with table values function

I write 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

the dbo.getrtmn is a table-value function and have a uniqueidentifier field named rtmid. prsnid is uniqueidentifier

When I run this SQL query cause error:

The multi-part identifier " tblprsn.prsnid" could not be bound.

Upvotes: 6

Views: 6681

Answers (2)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16894

instead of LEFT OUTER JOIN use OUTER APPLY with correleted subquery. The OUTER APPLY clause return all the rows on the left side table whether they return any rows in the table -valued-function or not, and thus 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

Demo on SQLFiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269523

This is your query:

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. This isn't allowed. First, I don't think the subquery is necessary. You are only selecting from the first table and doing a left outer join to keep all the records. The only affect of the subquery would be to multiply the rows. So, as you have written it, this query should do pretty much the same thing (except for duplicates):

SELECT tblprsn.prsnid, tblprsn.name 
FROM tblprsn;

You can do what you want in the original query, though, using apply rather than join:

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

Upvotes: 6

Related Questions