Issues with LEFT join on four tables

[Eric]

Hi Folks,
Lets assume I have three tables. Their layout is as follows. Please
note that tblPeople does not have an entry for Denver (this is my
problem)

tblCity
_________________
CityName OCID
LA 1
Denver 2

tblCars
_________________
OCID CarVolume
1 300,000
2 200,000

tblPeople
_________________
OCID PeopleVolume
1 1,200,345

tblDogs
_________________
OCID DogVolume
1 234,987
2 445,987

I'd like to run a quiery that returns the following data set:

CityName OCID CarVolume PeopleVolume DogVolume
LA 1 300000 1200345 234987
Denver 2 200000 null or 0 445997

My problem is the people table. Since there not entry for Denver, my
query is returning only a row for LA, nothing for Denver. Here's wht
my query looks like

Select tblCity.CityName, tblCity.OCID, tblCars.CarVolume,
tblPeople.PeopleVolume, tblDogs.DogVolume
FROM tblCity
LEFT JOIN tblCars on tblCity.OCID = tblCars.OCID
JOIN tblPeople on tblCars.OCID = tblPeople.OCID
JOIN tblDogs on tblPeople.OCID = tblDogs.OCID

This returns the following:
CityName OCID CarVolume PeopleVolume DogVolume
LA 1 300000 1200345 234987

I need this query to return a row for Denver as well. Any thoughts
anyone?
Your insight is greatly appreciated. ericlangland at hotmail.com

[Greg M.]

Eric,

If tblCity is your base (or primary) table, then you should be able to do
the following without problems. It appears you are very close to this
already.

select ci.ocid, ci.cityname, ca.carvolumn, p.peoplevolumn, d.dogvolumn
from tblcity ci
left join tblcars ca on ci.ocid = ca.ocid
left join tblpeople p on ci.ocid = p.ocid
left join tbldogs d on ci.ocid = d.ocid

HTH,

Greg
"Eric" <er**********@hotmail.com> wrote in message
news:53*************************@posting.google.co m...

Hi Folks,
Lets assume I have three tables. Their layout is as follows. Please
note that tblPeople does not have an entry for Denver (this is my
problem)

tblCity
_________________
CityName OCID
LA 1
Denver 2

tblCars
_________________
OCID CarVolume
1 300,000
2 200,000

tblPeople
_________________
OCID PeopleVolume
1 1,200,345

tblDogs
_________________
OCID DogVolume
1 234,987
2 445,987

I'd like to run a quiery that returns the following data set:

CityName OCID CarVolume PeopleVolume DogVolume
LA 1 300000 1200345 234987
Denver 2 200000 null or 0 445997

My problem is the people table. Since there not entry for Denver, my
query is returning only a row for LA, nothing for Denver. Here's wht
my query looks like

Select tblCity.CityName, tblCity.OCID, tblCars.CarVolume,
tblPeople.PeopleVolume, tblDogs.DogVolume
FROM tblCity
LEFT JOIN tblCars on tblCity.OCID = tblCars.OCID
JOIN tblPeople on tblCars.OCID = tblPeople.OCID
JOIN tblDogs on tblPeople.OCID = tblDogs.OCID

This returns the following:
CityName OCID CarVolume PeopleVolume DogVolume
LA 1 300000 1200345 234987

I need this query to return a row for Denver as well. Any thoughts
anyone?
Your insight is greatly appreciated. ericlangland at hotmail.com