Join one row only over three tables

Question

(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

score 6 · Accepted Answer · answered May 19 '16 at 05:59

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

Join one row only over three tables

1 Answers1