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

Question

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

Answer 1

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.

Answer 2

data

CREATE TABLE header(
   id       VARCHAR(100) NOT NULL 
  ,basic_no INTEGER  NOT NULL
);
INSERT INTO header
(id,basic_no) VALUES
('1a',1),
('2a',2),
('3a',3);

CREATE TABLE selling(
   basic_no   INTEGER  NOT NULL 
  ,curr       VARCHAR(100) NOT NULL
  ,sell_price INTEGER  NOT NULL
);
INSERT INTO selling
(basic_no,curr,sell_price) VALUES 
(1,'YEN',400),
(1,'USD',3),
(2,'YEN',10),
(3,'WON',75);


CREATE TABLE buying(
   basic_no  INTEGER  NOT NULL 
  ,curr      VARCHAR(100) NOT NULL
  ,buy_price INTEGER  NOT NULL
);
INSERT INTO buying
(basic_no,curr,buy_price) VALUES
(3,'WON',100),
(2,'USD',15);

use join with union instead of multiple join

select 
  id, 
  header.basic_no, 
  sell_price, 
  null buy_price 
from 
  header 
  join selling on header.basic_no = selling.basic_no 
union 
select 
  id, 
  header.basic_no, 
  null sell_price, 
  buy_price 
from 
  header 
  join buying on header.basic_no = buying.basic_no 
order by 
  id, 
  header.basic_no, 
  sell_price desc

dbfiddle