perform INNER JOIN on query results

[wave55]

Im using Access 97 and the query is complaining about a "syntax error missing operator". I suspect it has something to do with using two SELECTs for the INNER JOIN. INNER JOIN expects table names but I thought a SELECT returns a table.

SELECT * FROM Card WHERE Card.pk IN
(

(SELECT DISTINCT Card_Tag.cardFK FROM Card_Tag 
WHERE Card_Tag.tagFK IN (9,6) 
GROUP BY Card_Tag.cardFK 
HAVING COUNT(Card_Tag.cardFK) = 2)

INNER JOIN

(SELECT DISTINCT Card_Source.cardFK FROM Card_Source 
WHERE Card_Source.sourceFK IN (8,2,4) 
GROUP BY Card_Source.cardFK 
HAVING COUNT(Card_Source.cardFK) = 3)

ON Card_Tag.cardFK = Card_Source.cardFK

);

 

[r937]

I thought a SELECT returns a table

it do, but you gots to name it...

SELECT Card.* 
  FROM ( SELECT cardFK 
           FROM Card_Tag 
          WHERE tagFK IN (9,6) 
         GROUP 
             BY cardFK 
         HAVING COUNT(*) = 2 ) [COLOR=red]AS humpty[/color]
INNER 
  JOIN ( SELECT cardFK 
           FROM Card_Source 
          WHERE sourceFK IN (8,2,4) 
         GROUP 
             BY cardFK 
         HAVING COUNT(*) = 3 ) [COLOR=blue]AS dumpty[/color]
    ON humpty.cardFK = dumpty.cardFK
INNER
  JOIN Card  
    ON Card.pk = humpty.cardFK

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[wave55]

I've tried your code and errors popped up. Through trial and error I've found in A97 the identifier to the left of INNER JOIN must be a table name and not a query. Even using an alias will not work. This was probably changed in later versions of Access.

A kind soul on another forum pointed me in the right direction and this is the working statement:

SELECT * FROM Card WHERE pk IN
(
SELECT pk FROM Card 

INNER JOIN

[SELECT Card_Source.cardFK FROM Card_Source
WHERE Card_Source.sourceFK IN (6,7)
GROUP BY Card_Source.cardFK
HAVING COUNT(Card_Source.cardFK) = 2]. as q

ON Card.pk = q.cardFK

WHERE Card.pk IN
(
SELECT Card_Tag.cardFK FROM Card_Tag
WHERE Card_Tag.tagFK IN (9,6)
GROUP BY Card_Tag.cardFK
HAVING COUNT(Card_Tag.cardFK) = 2
)
);

Note how aliasing in A97 requires square brackets and a period. Versions of Access after 97 do not require such notation.