How to get only the first record on a LEFT OUTER JOIN ?

This is a very simple scenario, but is taken from a very complex one.
A Customer may have more than 1 address, some may have none. You only want each customer to appear only once. So what can you do?

SELECT C.Name, A.Address
FROM Customer AS C
OUTER APPLY (SELECT TOP 1 *
FROM Addresses
WHERE CID = C.ID) AS A

Note, that this will never work on SQL CE, but on SQL Server 2005 or 2008! And it’s not part of the ANSI standard!

2 comments on “How to get only the first record on a LEFT OUTER JOIN ?

  1. Thanks! This was exactly what I needed

    Beste regards,

    Stan (The Netherlands)

Hinterlasse eine Antwort

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind markiert *

Du kannst folgende HTML-Tags benutzen: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>