Mysterious problem

  • Thread starter Thread starter leodippolito
  • Start date Start date
L

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
 
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 :(
 
---
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?
Click to expand...

Because NumberOfReplies is not a column in the table, it's just an aggregate
value that you added to the SELECT. Try this for the WHERE clause instead:

WHERE (QUE.status = 1) AND (COUNT(REP.id) = 0)
 
Try to remove COUNT(REP.id) from your SELECT clause and add HAVING,
something like this (not tested):

SELECT QUE.id, QUE.description, QUE.status
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
HAVING COUNT(REP.id) = 0
 
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)
 
Back
Top