Joining SQL lookup table with data table

Question

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 ?

Answer 1

Try this:

SELECT
    orders.CityId
    cities.CityName
    orders.CustId,
    orders.CompletedOrders
    orders.PendingOrders
FROM
    orders
LEFT JOIN
    cities
ON
    orders.CityId = cities.CityId

It will work even if the corresponding city is not found, in which case CityName will be NULL.

Answer 2

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.

Answer 3

try this

select c.CityId,c.CityName,o.CustId,o.CompletedOrders,o.PendingOrders

from orders Left join cities 

on o.CityId = c.CityId