perform INNER JOIN on query results

[guest5006]

Im using Access 97 (anyone still remember 97?) and the query is complainingabout 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. It is possible to assign a table name to each of the 2 SELECTs and then do a INNER JOIN?

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

);

Table Card_Tag joins tables Card and Tag. Table Card_Source joins tables Card and Source.

 

[Bob Barrows]

Im using Access 97 (anyone still remember 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. It is
possible to assign a table name to each of the 2 SELECTs and then do
a INNER JOIN?

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

);

Table Card_Tag joins tables Card and Tag. Table Card_Source joins
tables Card and Source

The clauses are in the wrong order.

SELECT ...
FROM ... (all the tables and table expressions being selected from must be
here)
WHERE ....

Also, you must provide a table alias for the derived table (the subquery.

In A97, you had to use special syntax for derived tables:
[SELECT ...]. As alias
That's open-bracket, select statement, close-bracket, period, space, "As
alias"
Don't leave out the period!

Not too big a deal, but with GROUP BY, your records are already distinct so
using the DISTINCT keyword is overkill and useless.
Also, the table name in the derived table's query is not accessible to the
outer query. You must use the alias to qualify fields from the derived
table.

SELECT * FROM Card
INNER JOIN
[SELECT 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]. as q
ON Card_Tag.cardFK = q.cardFK
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
);


In A2000, they removed the requirement for the brackets and period, but you
still need to provide the table alias.
SELECT * FROM Card
INNER JOIN
(SELECT 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) as q
ON Card_Tag.cardFK = q.cardFK
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
);

 

[guest5006]

Also, you must provide a table alias for the derived table (the subquery.

In A97, you had to use special syntax for derived tables:
[SELECT ...]. As alias
That's open-bracket, select statement, close-bracket, period, space, "As
alias"
Don't leave out the period!

Not too big a deal, but with GROUP BY, your records are already distinct so
using the DISTINCT keyword is overkill and useless.
Also, the table name in the derived table's query is not accessible to the
outer query. You must use the alias to qualify fields from the derived
table.

Bob you saved my bacon. I bow to thee.
A few adjustments and this is the working query:

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
)
);