Mysterious problem

[leodippolito]

I have these tables:

Questions
- id (PK)
- description
- status

Replies
- id (PK)
- status
- idQuestion (FK)

I am trying to run this query within an ASP.NET application (OleDB):

---
SELECT QUE.id, QUE.description, QUE.status, COUNT(REP.id) AS
NumberOfReplies

FROM (Questions QUE LEFT OUTER JOIN (SELECT id, idQuestion FROM
Replies WHERE (status = 1)) REP ON QUE.id = REP.idQuestion)

WHERE (QUE.status = 1) AND (NumberOfReplies = 0)

GROUP BY QUE.id, QUE.description, QUE.status
---

I am receiving this error:

"no value given for one or more required parameters"

If I remove this part:

[AND (NumberOfReplies = 0)]

from the where clause, it works OK.

But I need that condition... NumberOfReplies = 0

What could be wrong?

TIA

 

[leodippolito]

I forgot to mention that the error code is -2147217904.

My goal is to have a list of questions without any associated reply
(NumberOfReplies = 0). The status of both questions and replies must be
1 (means published).

I don't understand why the NumberOfReplies condition is giving me that
error

 

[Gary Walter]

I can guess you are not working with
Access data since "LEFT OUTER JOIN"...

In Access, you cannot use an alias in a WHERE
clause, plus you cannot use an aggregate either.
So you would have to move the problem filter
to the HAVING clause.

SELECT
QUE.id,
QUE.description,
QUE.status,
COUNT(REP.id) AS NumberOfReplies
FROM
(Questions QUE
LEFT OUTER JOIN
(SELECT id, idQuestion FROM
Replies WHERE (status = 1)) REP
ON
QUE.id = REP.idQuestion)
WHERE
(QUE.status = 1)
GROUP BY
QUE.id,
QUE.description,
QUE.status
HAVING COUNT(REP.id) = 0

 

[leodippolito]

Thanks... This worked perfectly:

SELECT QUE.id, QUE.description, QUE.status, COUNT(REP.id) AS
NumberOfReplies
FROM (Questions QUE LEFT OUTER JOIN
(SELECT id, idQuestion
FROM Replies
WHERE (status = 1)) REP ON QUE.id =
REP.idQuestion)
WHERE (QUE.status = 1)
GROUP BY QUE.id, QUE.description, QUE.status
HAVING (COUNT(REP.id) = 0)