An Inner Join on an Inner Join

[David Wetmore]

In an M2M relationship between headers and quads I
wish to select the header keys & names where the header record
contains both of two quadkeys (CKey) from the join table, tblQuadsJoin.

I've been trying to do this with an inner join on an inner join
but the SQL below doesn't work.

SELECT tblHeaders.HKey, tblHeaders.Name
FROM tblHeaders
(INNER JOIN tblQuadsJoin
ON tblHeaders.HKey = tblQuadsJoin.PKey
WHERE tblQuadsJoin.CKey = 61)
INNER JOIN tblQuadsJoin
ON tblHeaders.HKey = tblQuadsJoin.PKey
WHERE tblQuadsJoin.CKey = 84

Am I on the right track or do I need to build an intermediate table
based on the first join and then do the second join on the intermediate table?

Thanks, Dave

 

[John Spencer]

As best as I can figure your query should look like the following.

SELECT tblHeaders.HKey, tblHeaders.Name
FROM tblHeaders INNER JOIN tblQuadsJoin
ON tblHeaders.HKey = tblQuadsJoin.PKey
WHERE tblQuadsJoin.CKey = 84 or
tblQuadsJoin.CKey = 61


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County