CODEWITHSUNDEEP
sqlsql-serverjoin
Cmaso
Cmaso

Reputation: 1113

LEFT JOIN to return at most one row for a 1-to-many relationship

I have a query in SQL Server where I'm doing multiple left joins. At one point I get to:

...LEFT JOIN foo on a.id = foo.id AND foo.code = 100

Up to that join statement in the query, a.id is not repeated; there's only 1 row for every a.id. But table a and foo have a 1-to-many relationship, so after that join statement I get multiple rows with the same a.id.

Table foo will always have multiple records where foo.id = a.id, but it may or may not have a record where foo.id = a.id AND foo.code = 100. If it does, there will be only 1 such record. So, what I want to see in the final query results is just 1 row for every a.id; if foo has a record where f.id = a.id and f.code = 100, then I want to see the date values from f.date1 and f.date2 from that record. If foo doesn't have such a record, then I just want to see NULL values for f.date1 and f.date2.

Edit from the comments:

It turns out that table foo actually can have multiple rows where a.id = foo.id and foo.code = 100.

Upvotes: 3

Views: 2353

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

One possible way to do it is to use OUTER APPLY:

SELECT
    a.id
    ,OA_foo.date1
    ,OA_foo.date2
FROM
    a
    OUTER APPLY
    (
        SELECT
            -- TOP(1)
            foo.date1
            ,foo.date2
        FROM foo
        WHERE
            foo.id = a.id
            AND foo.code = 100
        -- ORDER BY ...
    ) AS OA_foo
;

If you know for sure that the filter foo.id = a.id AND foo.code = 100 will always return only one row (or no rows), then you can leave the query as is.

If it is possible that nested query returns more than row and you want to leave only one row, then specify appropriate ORDER BY and TOP(1).

Upvotes: 2

Related Questions