Torbjörn Kalin Torbjörn Kalin - 6 months ago 8
SQL Question

Join one row only over three tables

(My question has been asked a lot of times with two tables involved, and has been answered here, here and here. But I can't figure out how to do the same with three tables involved.)

I have three tables, A, B and C, where A has many B and B has many C. I want to join these tables and select zero or one rows per A, which one should be based on a condition in C.

Example, assume that:

SELECT
a.aId
,b.bId
,c.cId
FROM
a
INNER JOIN b ON b.aId=a.aId
INNER JOIN c ON c.bId=b.bId
WHERE
c.someColumn='foo'


...yields the following result:

aId bId cId
=== === ===
1 11 101
1 12 102
1 12 103
2 21 201
2 21 203
2 22 202


...then I would like to, for instance, retrieve two distinct A-rows, the ones with highest
cId
.

aId bId cId
=== === ===
1 12 103
2 21 203

Answer

You can use ROW_NUMBER:

WITH Cte AS (
    SELECT
        a.aId,
        b.bId,
        c.cId,
        rn = ROW_NUMBER() OVER (PARTITION BY a.aId ORDER BY c.cId DESC)
    FROM a
    INNER JOIN b
        ON b.aId = a.aId
    INNER JOIN c
        ON c.bId = b.bId
    WHERE c.someColumn = 'foo'
)
SELECT
    aId, bId, cId
FROM Cte
WHERE rn = 1
Comments