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 ?

Answer 1

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;