SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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);
9 rows affected

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
Msg 4104 Level 16 State 1 Line 2
The multi-part identifier "a.basic_no" could not be bound.

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


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

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