I have following tables
Orders:
id | address1 | address2 | state
1 | 2 | 4 | Delivered
2 | 7 | 1 | Payment
Address:
id | city
1 | New York
2 | Paris
4 | London
7 | Berlin
Now I need a statement to get both cities with order status.
For example order ID 1 should output: Delivered | Paris | London
I tried the following statement:
SELECT orders.state, address.city FROM orders
LEFT JOIN address
ON orders.address1 = address.id OR orders.address2 = address.id;
It obviously only outputs one address city but I want both.
Any idea how the statement should look like?
You can do the self join:
select o.status, ad.city, ad1.city
from Orders o
left join Address ad on ad.id = o.address1
left join Address ad1 on ad1.id = o.address2;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments