UNION in Subquery

[Guest]

Ok, I can do the following


SELECT COUNT(cq.qid)
FROM completed_questions cq

UNION

SELECT cq.qid
FROM completed_questions cq


The problem with this on is taht it will return the count but also the
cq.qids from the other query in the union.

But I can't do the following


SELECT COUNT(cq.cqid)
FROM completed_questions cq
WHERE cq.cqid IN (

SELECT cq.qid
FROM completed_questions cq

UNION

SELECT cq.qid
FROM completed_questions cq)



This query should fix the above problem but it doesn't. Access tells me that
"This operation is not allowed in subqueries."
I could be wrong but by using simple queries like this one, i think i've
come to the conclusion that UNION cannot be used in a subquery when COUNT is
in the main query. How do i get around this. UNION was the only way i could
combine the two queries into one. Once i've combined those queries, I want to
count the number of cqids that appear. I can use COUNT and a subquery, but i
can't use COUNT and UNION in a subquery. Or maybe I just can't use UNION in a
subquery. Either way, i have this big, complex query that i don't want to
start over on.

Any ideas?

 

[Van T. Dinh]

I can't see the point of using:

.....
SELECT cq.qid
FROM completed_questions cq

UNION

SELECT cq.qid
FROM completed_questions cq

Both sides of the Union is exactly the same and since you used UNION and not
UNION ALL, one qid of each pair of qid from the left and right sides of the
Union will be eliminated. Hence, the above is equivalent to simply:

SELECT cq.qid
FROM completed_questions cq


Describe in words what you try to get, a small sample set of data and the
return of the query required.