Problem With Nested Joins

[Don]

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

 

[Ken Snell [MVP]]

Try this (not tested):

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

As Bob suggested in the other thread, you can build the query you want in
QBE view of query design, and then change to SQL view and copy the
statement. I often do this for nested joins when I'm not sure exactly the
order for the joins and tables. If the above isn't quite successful, I'd try
the QBE approach.

 

[Don]

Ken,

I think I have mis-formulated my objective! After working with your
suggestion, reviewing a couple of references, and pondering my naval, I
believe the SQL is doing exactlly what it should. My problem is my
expectation. My objective is to have a list of people who have moved from a
particular building and list only the work number if it is available.

The problem is not so much an issue if a resident has no numbers listed (I
think this is what OUTER JOINS really address), but if they only have
numbers other than 'Work' listed. If none of the numbers are a 'Work'
number, I still want the person included in the final list. However, if
there is a 'Work' number, I want to include that information. In this
situation, the OUTER JOIN includes the phone number (e.g. 'Reference'), but
then the record is removed due to the fact that 'Work' <> 'Reference'.

Does this make any sense? My brain cell is becoming Jello-like trying to
wrap my head around this!!

I think it is time to take a walk and regroup!

Thanks for making me really think about this!

Don

 

[Ken Snell [MVP]]

Ah, that helps.

Now I am not 100% sure I'm understanding what you want, but let's start with
getting all the residents for your search first.

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
WHERE A.Building = 3
AND R.Status = 'Moved';


Right?

Then what you're wanting is to list the phone number if it's a Work number,
right? So for this I would use a subquery to get the phone value if it's a
work number:

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


--

Ken Snell
<MS ACCESS MVP>

 

[Don]

Ken,

You nailed it on the head!!

One thing I do not understand in the subquery is how the work phone number
is associated with the resident. Is it an implied JOIN in the WHERE
statement (P.ResID = R.ResID)?

(SELECT P.Number
FROM tblPhones AS P
WHERE P.Type = 'Work' AND
P.ResID = R.ResID) AS WorkPhoneNumber

Or is the subquery "executed" for each record in tblResidents?

I greatly appreciate your help with this!!

Thanks!

Don

 

[Ken Snell [MVP]]

The subquery is executed for each value of R.ResID. But it's not actually
joining the two tables. Instead, what you're doing is passing the value from
R.ResID to the query as a "constant" that is used in the WHERE clause, just
as the 'Work' constant is used there; think of it as being more similar to a
variable that is passing its current value to the subquery.

 

[Ken Snell [MVP]]

Then again, I suppose one might think of this as an implied join... I'm not
an SQL guru, so take my "explanations" with a bit of skepticism at heart. <
g >

I should note, so long as we're on the subject, that you can replace the
subquery with the DLookup domain function in this case. It's designed to do
the same thing:

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

However, I often use a subquery in place of DLookup (and other domain
functions) because my experience has been that subqueries tend to run a bit
faster than the domain functions, and in a query that returns lots of
records, this time differential can be significant.
--

Ken Snell
<MS ACCESS MVP>

 

[Don]

Ken,

I think I got it! I'm going to look through a SQL book I have at home
tonight.

Again, thanks for all your help!

Don

 

[Ken Snell [MVP]]

One other note... DLookup will work only from within ACCESS, as it's an
ACCESS VBA function.

--

Ken Snell
<MS ACCESS MVP>

Then again, I suppose one might think of this as an implied join... I'm
not an SQL guru, so take my "explanations" with a bit of skepticism at
heart. < g >

I should note, so long as we're on the subject, that you can replace the
subquery with the DLookup domain function in this case. It's designed to
do the same thing:

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

However, I often use a subquery in place of DLookup (and other domain
functions) because my experience has been that subqueries tend to run a
bit faster than the domain functions, and in a query that returns lots of
records, this time differential can be significant.
--

Ken Snell
<MS ACCESS MVP>

The subquery is executed for each value of R.ResID. But it's not actually
joining the two tables. Instead, what you're doing is passing the value
from R.ResID to the query as a "constant" that is used in the WHERE
clause, just as the 'Work' constant is used there; think of it as being
more similar to a variable that is passing its current value to the
subquery.