help with subquery

[corey]

This query is for an application I'm developing.

SELECT COUNT(*) AS N_POST
FROM POSTS
WHERE TOPICID =
(SELECT TOPICID FROM
TOPICS
WHERE FORUMID = '00001')

I get this error.
At most one record can be returned by this subquery.

I'm tring to get the count of posts in the posts table with the topicid from
the forum table where the forumid = 0001 you follow....:)

I know there is a way around this but I haven't used it in a while. can
someone help me out?

[Q*bert]

So a forum can have many topics and a topic can have many posts...
if this is the case... when you say forumid = '00001' you are returning several
topic ids One way to resolve this problem is to use the word 'in' instead
of =
so

WHERE TOPICID IN (Select...)

The use of IN does take more resources than normal so if peformance is a
question...

A better method would be to fully join all the tables if possible
Assuming your table structure is similar to to the following
POSTS Table TOPICS Table
----------- ----------
PostID TopicID
TopicID ForumID
... ...

SELECT count(PostID) as N_Post
FROM POSTS INNER JOIN Topics ON Posts.TopicID = Topics.TopicID AND Topics.ForumID
= '00001'

if the "AND Topics.Formid ='00001'" is not supported in your DB... use it
in a where clause.

FROM POSTS INNER JOIN Topics ON Posts.TopicID = Topics.TopicID
WHERE Topics.ForumID = '00001'

but setting the conditions on the join is faster then doing it in the where
clause.

Hope This helped
Q*bert
*(@#^$


"corey" <charbaugh1000@hotmail.com> wrote:
>
>This query is for an application I'm developing.
>
>SELECT COUNT(*) AS N_POST
>FROM POSTS
>WHERE TOPICID =
>(SELECT TOPICID FROM
>TOPICS
>WHERE FORUMID = '00001')
>
>I get this error.
>At most one record can be returned by this subquery.
>
>I'm tring to get the count of posts in the posts table with the topicid
from
>the forum table where the forumid = 0001 you follow....:)
>
>I know there is a way around this but I haven't used it in a while. can
>someone help me out?