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

jchrr9hc  于 2023-06-21  发布在  SQL Server
关注(0)|答案(2)|浏览(127)

So I have 3 tables, which is

Table 1 (header)
| id | basic_no |
| ------------ | ------------ |
| 1a | 1 |
| 2a | 2 |
| 3a | 3 |

Table 2 (selling)

basic_nocurrsell_price
1YEN400
1USD3
2YEN10
3WON75

Table 3 (buying)

basic_nocurrbuy_price
3WON100
2USD15

My Expectation

idbasic_nosell_pricebuy_price
1a1400null
1a13null
2a210null
2a2null15
3a375null
3a3null100

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:

idbasic_nosell_pricebuy_price
1a1400100
1a140015
1a13100
1a1315
2a210100
2a21015
3a37515
3a375100

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

vshtjzan

vshtjzan1#

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:

idbasic_nosell_pricebuy_price
1a1400NULL
1a13NULL
2a210NULL
2a2NULL15
3a375NULL
3a3NULL100

Please feel free to add an appropriate ORDER BY clause.

cuxqih21

cuxqih212#

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

相关问题