CODEWITHSUNDEEP
sqlsql-serverjoinleft-joinouter-join
asmgx
asmgx

Reputation: 8004

Select top 1 of table

Trying to select top record of a outer joined table

if there are no records in table B then null will be there

if there are multiple records then just the first one should be selected.

I built this query but I get error 

 SELECT DISTINCT
        A.Col1 , A.Col2, B.Col2, B.Col3
 FROM   
        A LEFT OUTER JOIN (SELECT TOP 1 * FROM B WHERE B.Col1=A.Col1) A ON B.Col1=A.Col1

The multi-part identifier "B.Col1" could not be bound.

Anyone know how to resolve this?

Upvotes: 0

Views: 81

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

If you want only one match, then use outer apply:

SELECT A.Col1 , A.Col2, B.Col2, B.Col3
FROM A OUTER APPLY
    (SELECT TOP 1 *
     FROM B
     WHERE B.Col1 = A.Col1
    ) B;

Upvotes: 2

Related Questions