Inner Join Syntax Problems

[David Wetmore]

I am beginning the construction of a minimal search via SQL involving three sets of search terms.
The three sets of search terms are each M2M with the main table (tblHeaders) through link tables.
I have SQL statements which return the hits on each set of search terms.

I am assuming than the inner join of the three sets will give me a minimal set of hits.
I am trying to build the SQL statement sequetially, beginning with the first set of search terms which has tblQuads.Join
as the link table. The SQL with the first set is:

SELECT thlHeaders.HKey, tblHeaders.Name FROM tblHeaders
INNER JOIN
SELECT HKey FROM tblHeaders
WHERE (tblHeaders.HKey
IN
(SELECT PKey AS HKey
FROM tblQuadsJoin
WHERE (((tblQuadsJoin.CKey)=59))))
ON tblHeaders.HKey = HKey)

I have tested the nested SELECT clause with a recordset, and it retrieves the proper HKey values from tblHeaders.

I am getting a syntax error in the FROM clause. Am I missing some parentheses, or is it a more fundamental issue?
(SQL reminds me of LISP!)

Thanks, Dave

 

[Dale Fye]

David,

It looks like you are trying to get the HKey and Name from tblHeaders, where
the PKey in tblQuadsJion matches the HKey in tblHeaders, and where the CKey
in tblQuadsJoin = 59. If so, a simpler SQL would look like:

SELECT tblHeaders.HKey, tblHeaders.Name
FROM tblHeaders
INNER JOIN tblQuadsJoin
ON tblHeaders.HKey = tblQuadsJoin.PKey
WHERE tblQuadsJoin.CKey = 59

HTH
Dale