SQL Joining the first, second and third row of joining table

Question

I need to display some columns on the final result which comes from a join. Those columns comes in 3 groups, say:

name_1, addr_1, prefecture_1, price_1,
name_2, addr_2, prefecture_2, price_2,
name_3, addr_3, prefecture_3, price_3

Where these 3 groups comes from a join which should return only 3 records according to a sort key carrier_cd2. The _1 group is the first row, _2 group the second and _3 is the third row.

My question is pretty much the same as this SO question but I need 3 records instead of 2.

I have come up with my initial SQL query:

SELECT
    inner_t12.contract_no,
    inner_t13.carrier_cd2,
    inner_t13.addr,
    inner_t13.prefecture,
    inner_t13_2.carrier_cd2,
    inner_t13_2.addr,
    inner_t13_2.prefecture,
    inner_t13_3.carrier_cd2,
    inner_t13_3.addr,
    inner_t13_3.prefecture
FROM
    処分業者契約マスタ inner_t12
    LEFT OUTER JOIN 処分契約委託運搬業者マスタ inner_t13
        ON inner_t12.contract_no = inner_t13.contract_no
    JOIN 処分契約委託運搬業者マスタ inner_t13_2
        ON inner_t13_2.contract_no = inner_t13.contract_no
        AND inner_t13_2.carrier_cd2 > inner_t13.carrier_cd2
    JOIN 処分契約委託運搬業者マスタ inner_t13_3
        ON inner_t13_3.contract_no = inner_t13.contract_no
        AND inner_t13_3.carrier_cd2 > inner_t13_2.carrier_cd2

The screenshot

It returns the required rows on the final result set, however it need to be left outer join so that it will still display rows even if the join does not produce the required 3 rows, even 2 or 1 or none should work and display NULL instead.

Hope you can give some hints on this.

Thanks in advanced.

SQL Server 2005 Express

Answer 1

If I understand the question I think you're looking for this. Bracket the two inner joins inside the left join

FROM
    inner_t12
    LEFT OUTER JOIN inner_t13
    (    
    INNER JOIN  inner_t13_2
        ON inner_t13_2.contract_no = inner_t13.contract_no
        AND inner_t13_2.carrier_cd2 > inner_t13.carrier_cd2
    INNER JOIN  inner_t13_3
        ON inner_t13_3.contract_no = inner_t13.contract_no
        AND inner_t13_3.carrier_cd2 > inner_t13_2.carrier_cd2
)
ON inner_t12.contract_no = inner_t13.contract_no

Answer 2

If you mean just 3 rows (3 records), Just add "TOP 3" in your SELECT statement.
See below.

SELECT
    TOP 3
    inner_t12.contract_no,
    inner_t13.carrier_cd2,
    inner_t13.addr,
    inner_t13.prefecture,
    inner_t13_2.carrier_cd2,
    inner_t13_2.addr,
    inner_t13_2.prefecture,
    inner_t13_3.carrier_cd2,
    inner_t13_3.addr,
    inner_t13_3.prefecture
FROM
    処分業者契約マスタ inner_t12
    LEFT OUTER JOIN 処分契約委託運搬業者マスタ inner_t13
        ON inner_t12.contract_no = inner_t13.contract_no
    JOIN 処分契約委託運搬業者マスタ inner_t13_2
        ON inner_t13_2.contract_no = inner_t13.contract_no
        AND inner_t13_2.carrier_cd2 > inner_t13.carrier_cd2
    JOIN 処分契約委託運搬業者マスタ inner_t13_3
        ON inner_t13_3.contract_no = inner_t13.contract_no
        AND inner_t13_3.carrier_cd2 > inner_t13_2.carrier_cd2

Answer 3

I think I solved it (sort of). What I did was use ROW_NUMBER and OVER() on the ON clause of a JOIN.

use OurProjectDb
go

SELECT
    t12.disposal_cd,

    t21_1.name_kanji AS name_kanji_1,
    t13_1.prefecture AS prefecture_1,
    t13_1.addr AS addr_1,
    t13_1.permission_no AS permission_no_1,
    t13_1.vehicle_type AS vehicle_type_1,
    t13_1.transport_price AS transport_price_1,

    t21_2.name_kanji AS name_kanji_2,
    t13_2.prefecture AS prefecture_2,
    t13_2.addr AS addr_2,
    t13_2.permission_no AS permission_no_2,
    t13_2.vehicle_type AS vehicle_type_2,
    t13_2.transport_price AS transport_price_2,

    t21_3.name_kanji AS name_kanji_3,
    t13_3.prefecture AS prefecture_3,
    t13_3.addr AS addr_3,
    t13_3.permission_no AS permission_no_3,
    t13_3.vehicle_type AS vehicle_type_3,
    t13_3.transport_price AS transport_price_3
FROM
    処分業者契約マスタ t12
    LEFT OUTER JOIN
    (
        処分契約委託運搬業者マスタ t13_1
        LEFT OUTER JOIN 委託運搬業者マスタ t21_1 ON t13_1.carrier_cd2 = t21_1.carrier_cd2
    )
    ON t13_1.contract_no = t12.contract_no AND t13_1.carrier_cd2 = (
        SELECT TOP 1
            inner_t13_1.carrier_cd2
        FROM
            処分契約委託運搬業者マスタ inner_t13_1
        WHERE
            inner_t13_1.contract_no = t12.contract_no
        ORDER BY
            inner_t13_1.carrier_cd2 ASC
    )
    LEFT OUTER JOIN
    (
        処分契約委託運搬業者マスタ t13_2
        LEFT OUTER JOIN 委託運搬業者マスタ t21_2 ON t13_2.carrier_cd2 = t21_2.carrier_cd2
    )
    ON t13_2.contract_no = t12.contract_no AND t13_2.carrier_cd2 = (
        SELECT
            carrier_cd2
        FROM
        (
            SELECT TOP 2
                ROW_NUMBER() OVER (ORDER BY inner_t13_2.carrier_cd2) AS row_num,
                inner_t13_2.carrier_cd2
            FROM
                処分契約委託運搬業者マスタ inner_t13_2
            WHERE
                inner_t13_2.contract_no = t12.contract_no
            ORDER BY
                inner_t13_2.carrier_cd2
        ) top2join
        WHERE
            row_num = 2
    )
    LEFT OUTER JOIN
    (
        処分契約委託運搬業者マスタ t13_3
        LEFT OUTER JOIN 委託運搬業者マスタ t21_3 ON t13_3.carrier_cd2 = t21_3.carrier_cd2
    )
    ON t13_3.contract_no = t12.contract_no AND t13_3.carrier_cd2 = (
        SELECT
            carrier_cd2
        FROM
        (
            SELECT TOP 3
                ROW_NUMBER() OVER (ORDER BY inner_t13_3.carrier_cd2) AS row_num,
                inner_t13_3.carrier_cd2
            FROM
                処分契約委託運搬業者マスタ inner_t13_3
            WHERE
                inner_t13_3.contract_no = t12.contract_no
            ORDER BY
                inner_t13_3.carrier_cd2
        ) top3join
        WHERE
            row_num = 3
    )