Issues with LEFT join on four tables

**Greg M.** · Jul 20 '05, 03:06 AM

Re: Issues with LEFT join on four tables

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" <ericlangland@h otmail.com> wrote in message
news:5372437.04 03041742.6259df ad@posting.goog le.com...[color=blue]
> 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.CityNam e, tblCity.OCID, tblCars.CarVolu me,
> tblPeople.Peopl eVolume, tblDogs.DogVolu me
> 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[/color]

Issues with LEFT join on four tables

Issues with LEFT join on four tables

Comment