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
2条答案
按热度按时间vshtjzan1#
It appears you want
selling
andbuying
records on separate rows. You needUNION
for that.Joining this with
header
gives the following query.Running this against the following data:
Gives the following result set:
Please feel free to add an appropriate
ORDER BY
clause.cuxqih212#
data
use
join
withunion
instead ofmultiple join
dbfiddle