Re: Proper Application of a Subquery

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: James Goodwin (jim.goodwin_at_midmichigan.org)
Date: 02/25/05 

Date: Fri, 25 Feb 2005 16:46:23 -0500

"MGFoster" <me@privacy.com> wrote in message
news:LIMTd.5610$MY6.682@newsread1.news.pas.earthlink.net...
> SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status
>
> FROM (tblApartments As A
> INNER JOIN tblResidents As R
> ON A.AptNum = R.AptNum)
> LEFT JOIN tblPhone As P
> ON R.ResID = P.ResID
>
> WHERE A.Building = 3
> AND R.Status = "Moved"
> AND P.Type = "Work"
>
> I used a LEFT JOIN on the tblPhone in case a resident did not have a
> Work phone number.

I believe that if you want to see all residents even if they have no work
phone that you need to add the P.type condition to the Join clause as:

SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status
FROM (tblApartments As A
      INNER JOIN tblResidents As R
        ON A.AptNum = R.AptNum)
      LEFT JOIN tblPhone As P
        ON R.ResID = P.ResID and P.Type = "Work"
WHERE A.Building = 3
   AND R.Status = "Moved"

Good Luck,
Jim