Select buyers and sellers data from customer

Question

I have two tables Customer and Market

Select * from Customer :

customer_id  | f_name  | l_name
-------------------------------
1            | Sam     | Brow
2            | Alex    | Fore
3            | Marc    | Lor
4            | Fab     | Sow

Select * from Market

Orderid  | Product  | SellerID  | BuyerID 
-----------------------------------------
5        | Apple    | 1         | 2
6        | Juice    | 3         | 4

When doing this SELECT to have Sellers and buyers data, I have data of all customers.

SELECT c.f_name, c.l_name ,m.Orderid
FROM Customer c
INNER JOIN Market m ON m.BuyerID = c.customer_id OR m.SellerID = c.customer_id

Instead, I need to separate the data of buyers on their own and sellers on their own. I'd expect something like this :

Orderid  | Seller_f_name  | Buyer_f_name 
----------------------------------------
5        | Sam            | Alex
6        | Marc           | Fab

Any idea please ?

This may help you [SQL Replace multiple variables from another table in query result](https://stackoverflow.com/questions/25365327/sql-replace-multiple-variables-from-another-table-in-query-result) — Jimmy, Jan 24 '22 at 10:01

score 0 · Accepted Answer · answered Jan 24 '22 at 09:59

0

You need to join the market table twice with customer table -

SELECT Orderid, C1.f_name Seller_f_name, C2.f_name Buyer_f_name 
  FROM Market M
  LEFT JOIN Customer C1 ON M.SellerID = C1.customer_id
  LEFT JOIN Customer C2 ON M.BuyerID = C2.customer_id;

answered Jan 24 '22 at 09:59

Ankit Bajpai

13,128
4
25
40

Thank you for your help. Please advise on why did you replaced INNER JOIN in the example to LEFT JOIN ? – AMmra Jan 24 '22 at 10:04
You can use Inner join also if that solves your purpose. – Ankit Bajpai Jan 24 '22 at 10:13

Select buyers and sellers data from customer

1 Answers1