Problem With Nested Joins

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Don (someone_at_somewhere.net)
Date: 02/26/05 

Date: Sat, 26 Feb 2005 13:24:32 -0500

Thanks to some help from Bob Barrows, I got the following query working in
Access 2003:

SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status, P.Number,
P.Type
FROM tblApartments AS A
INNER JOIN (tblPhones AS P
     INNER JOIN tblResidents AS R
     ON P.ResID = R.ResID)
     ON R.AptNum = A.AptNum
WHERE A.Building = 3
     AND R.Status = 'Moved'
     AND P.Type = 'Work';

One difficulty I ran into was that people without a work number were not
being listed. Figured "no problem" just use a LEFT JOIN and away I go.
However using 'LEFT' or 'LEFT OUTER' does not work.

SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status, P.Number,
P.Type
FROM tblApartments AS A
INNER JOIN (tblPhones AS P
     LEFT OUTER JOIN tblResidents AS R
     ON P.ResID = R.ResID)
     ON R.AptNum = A.AptNum
WHERE A.Building = 3
     AND R.Status = 'Moved'
     AND P.Type = 'Work';

results in an "Join expression not Supported." error. But if I understand
the following from
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp

"OUTER JOINs can be nested inside INNER JOINs in a multi-table join, but
INNER JOINs cannot be nested inside OUTER JOINs."

I would assume the JOIN between Residents and PhoneNumbers is the inside of
the nesting. For a quick test I made the first INNER JOIN a LEFT OUTER JOIN
and got a syntax error. So it sort of supports my assumption.

On a related topic, is the Access SQL syntax (specifically the nesting of
joins) compatible with SQL Server syntax?

Any help will be greatly appreciated!!

Thanks!

Don

Relevant Pages

  • Re: Relational question
    ... left outer join ... >) as AB inner join ( ... >>Let's look at the first LEFT JOIN query: ... >>which we'll refer to collectively as and the constituent queries ...
    (microsoft.public.sqlserver.programming)
  • Re: Close vs "nothing"
    ... >> The result is therefore the same as an inner join. ... >> For more info about handling nulls, ... >>> with info from many locations holding period information. ... >>> I am trying left outer join with dates table on left and data on right ...
    (microsoft.public.access.modulesdaovba)
  • Problem With Nested Joins
    ... FROM tblApartments AS A ... INNER JOIN (tblPhones AS P ... INNER JOIN tblResidents AS R ... For a quick test I made the first INNER JOIN a LEFT OUTER JOIN ...
    (microsoft.public.access.queries)
  • Re: MULTIPLE JOINS
    ... An inner join is like a cross join to which we apply a where clause: ... An outer join decides to keep all the records from ONE of the tables. ... If the logical equivalent WHERE would ... There are two other types of outer joins not supported in Jet. ...
    (microsoft.public.access.queries)
  • Re: Can someone please explain this ?
    ... INSERT Test1 (Number1, Letter1) ... INNER JOIN Test2 ... LEFT OUTER JOIN Test2 ...
    (microsoft.public.sqlserver.programming)