I have a lookup table say cities with fields CityId, CityName
CityId CityName
1 New York
2 San Francisco
3 Chicago
I have an orders table which has fields: CityId, CustId, CompletedOrders, PendingOrders
CityId CustId CompletedOrders PendingOrders
1 123 100 50
2 123 75 20
I want a table/report that lists orders details of a given customer in all cities, i.e. the result I need is:
CityId CityName CustId CompletedOrders PendingOrders
1 New York 123 100 50
2 San Francisco 123 75 20
3 Chicago 123 0 0
How to do that ?
SELECT
c.CityId
c.CityName
o.CustId,
o.CompletedOrders
o.PendingOrders
FROM cities c
LEFT JOIN orders o ON ( c.CityId = o.CityId )
This will return all the rows that you want, but for the rows that don't exist in details
it will return NULL
values, so you would get:
CityId CityName CustId CompletedOrders PendingOrders
1 New York 123 100 50
2 San Francisco 123 75 20
3 Chicago 123 NULL NULL
The solution to get 0
instead depends on your database. With MySQL use IFNULL
, with Oracle use NVL
.
try this
select c.CityId,c.CityName,o.CustId,o.CompletedOrders,o.PendingOrders
from orders Left join cities
on o.CityId = c.CityId
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With