Search code examples
sqlsql-serversql-query-store

How to avoid multiple value after joining three tables in SQL Server?

So I have 3 tables, which is

Table 1 (header)

id basic_no
1a 1
2a 2
3a 3

Table 2 (selling)

basic_no curr sell_price
1 YEN 400
1 USD 3
2 YEN 10
3 WON 75

Table 3 (buying)

basic_no curr buy_price
3 WON 100
2 USD 15

My Expectation

id basic_no sell_price buy_price
1a 1 400 null
1a 1 3 null
2a 2 10 null
2a 2 null 15
3a 3 75 null
3a 3 null 100

The problem is, I've been trying using LEFT JOIN on multiple tables but the output didn't met my expectations.

My Query:

SELECT h.basic_no, s.sell_price, b.buy_price
FROM header h LEFT JOIN selling s on a.basic_no = s.basic_no
LEFT JOIN buying b on h.basic_no = b.basic_no

My Output:

id basic_no sell_price buy_price
1a 1 400 100
1a 1 400 15
1a 1 3 100
1a 1 3 15
2a 2 10 100
2a 2 10 15
3a 3 75 15
3a 3 75 100

It should has 4+2 = 6 rows, but my query gave me 4*2 = 8 rows. Kindly need your help. Thank you

Solution

  • It appears you want selling and buying records on separate rows. You need UNION for that.

    SELECT basic_no, sell_price, NULL AS buy_price
FROM selling
UNION ALL
SELECT basic_no, NULL, buy_price
FROM buying

    Joining this with header gives the following query.

    SELECT h.id, h.basic_no, sb.sell_price, sb.buy_price
FROM header h
LEFT OUTER JOIN (
    SELECT basic_no, sell_price, NULL AS buy_price
    FROM selling
    UNION ALL
    SELECT basic_no, NULL, buy_price
    FROM buying
) sb ON sb.basic_no = h.basic_no

    Running this against the following data:

    CREATE TABLE header (id char(2), basic_no int)
INSERT INTO header VALUES ('1a', 1), ('2a', 2), ('3a', 3)

CREATE TABLE selling (basic_no int, curr char(3), sell_price int)
INSERT INTO selling VALUES (1, 'YEN', 400), (1, 'USD', 3), (2, 'YEN', 10), (3, 'WON', 75)

CREATE TABLE buying (basic_no int, curr char(3), buy_price int)
INSERT INTO buying VALUES (3, 'WON', 100), (2, 'USD', 15)

    Gives the following result set:

    id basic_no sell_price buy_price
    1a 1 400 NULL
    1a 1 3 NULL
    2a 2 10 NULL
    2a 2 NULL 15
    3a 3 75 NULL
    3a 3 NULL 100

    Please feel free to add an appropriate ORDER BY clause.