CODEWITHSUNDEEP
sqlsql-serversubqueryinner-join
Jhorra
Jhorra

Reputation: 6321

Multi-part identifier could not be bound on inner join with sub query

I'm trying to join a main table with just the first record of a related table so I only get one result back

SELECT 
    * 
FROM 
    admissions as a
    inner join (select 
            top 1 * 
        from 
            admissionroom 
        where 
            admissionroom.admissionid = a.id 
        order by 
            admissionroom.startdate asc) as r on
        a.id = r.admissionid

I've stripped out all other parts of the query as this is the piece causing the error.

Upvotes: 1

Views: 1750

Answers (1)

Lamak
Lamak

Reputation: 70638

You can't do that on a JOIN, you need to use CROSS APPLY:

SELECT * 
FROM admissions as a
CROSS APPLY (SELECT TOP 1 * 
             FROM admissionroom 
             WHERE admissionid = a.id 
             ORDER BY startdate ASC) as r

Upvotes: 4

Related Questions