Using SELECT results for INNER JOIN

[jolly green]

Hello. 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

);

 

[plog]

For debugging purposes I would take the second SELECT statement and make it its own query with its own name. I would run it to make sure it works, then I would use it, instead of the SQL you now have, in the query you posted above.

However, when you get it syntactically correct, it will return 0 results. The first SELECT statement will return results for 6 & 9 while the second SELECT statement will return results for 2,4 & 8.

The first part (6 & 9) will never make a match to any of the second part (2,4 & 8) so your query will always be empty.

 

[jolly green]

So you're saying replace the entire statement with just the second SELECT statement? If I delete the first SELECT statement I will not be able to perform a AND query on both tables.

On the issue of getting 0 results.
The first SELECT refers to join table Card_Tag and queries on [tagFK]. The second SELECT refers to join table Card_Source and queries on [sourceFK].
Table Card is 1:N with Tag.
Table Card is 1:N with Source.
Card records can have different tags and sources.

 

[plog]

My methodology is to make a query with this SQL:

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;

Save it and call it 'subQuery'

Then for the query you want to achive that you first posted, use this SQL:

SELECT DISTINCT Card_Tag.cardFK
FROM Card_Tag INNER JOIN subQuery ON Card_Tag.cardFK = subQuery.cardFK
WHERE (((Card_Tag.tagFK) In (9,6)))
GROUP BY Card_Tag.cardFK
HAVING (((Count(Card_Tag.cardFK))=2));

After looking at your tables closer, I was wrong about 0 results.

 

[VilaRestal]

You need to give the subqueries an alias each (none of their component tables actually exist in the overall query so can't be referred to outside of the subqueries):

SELECT * FROM Card WHERE Card.pk IN
(

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

INNER JOIN

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

ON X.cardFK = Y.cardFK

);

I must say each subquery looks wrong to my eyes:

SELECT DISTINCT field
GROUP BY field
HAVING COUNT(field) = 2

I think the DISTINCT is unnecessary. It's already grouping by that only field.

 

[jolly green]

You need to give the subqueries an alias each ...

Thanks for the suggestion on aliasing the query results. I have tried this

SELECT * FROM Card WHERE Card.pk IN
(

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

INNER JOIN 

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

ON X.cardFK = Y.cardFK

);

Access 97 still complains of a "syntax error missing operator" and highlighted the first "AS" keyword.

Moving code around I also tried running the queries in sequence separated by commas and again with semi-colons:

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

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

SELECT * FROM Card WHERE Card.pk IN
(

X INNER JOIN Y

ON X.cardFK = Y.cardFK

);

which yielded this error "Syntax error in union query".

 

[jolly green]

My methodology is to make a query with this SQL:

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;

Save it and call it 'subQuery'

I didn't mention it in the first post but the numbers in parenthesis are chosen at runtime by the user. While in my post I used (8,2,4) it could be whatever the user chooses. I think I may just endup doing the INNER JOIN by code. At worst it will be O(n*n) search.

 

[VilaRestal]

I'm not sure what Access 97 allows in queries. It's been a while since I used that and I wouldn't have tried anything as sophisticated as this back then.

I think my suggestion would be similar to plog's: Save the subqueries as queries in their own right and use them in this query (that's in effect what the alias should be doing). And of course if either of those produce a syntax error then that will help to narrow it down.

Oh and definitely not that 2nd version with the cartesian join - that's not right.

__________________________________________________

Edit: But if it's being built at runtime then you can't save the subqueries except by creating query defs and then using them.

 

[jolly green]

The problem has been solved. A user on another forum has provided the solution. The clauses have been reworked and in Access97 aliasing a table requires square brackets and a period. This is alot of hassle all because A97 does not support INTERSECT.

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