Gossamer Forum
Home : General : Databases and SQL :

subquery problem

Quote Reply
subquery problem
I'm making a basic discussion board program (no categories). What I'm trying to do is get as much info about a thread as I can with a single query, and so far I been able to get data from my threads, posts, and users table with:

select threads.id as thread_id,
threads.updated as last_post_time,
threads.last_post_id as last_post_id,
users.user_name as user_name,
posts.subject as subject,
posts.user_id as user_id
from threads, users, posts
where threads.post_id = posts.id and
posts.user_id = users.id
order by last_post_time desc

but I also want to get a count of the number of posts within the thread in the same query, instead of my having to do:

$rec->{posts} = $dbh->selectrow_array("select count(*) from posts where posts.thread_id = $rec->{thread_id}");

for each record.

Any ideas on how I can squeeze this into my existing query?

Limecat is not pleased.
Quote Reply
Re: [fuzzy logic] subquery problem In reply to
ugh.. duh... don't know why I didn't try just adding:

I now have:
SELECT threads.id AS thread_id,
threads.updated AS last_post_time,
threads.last_post_id AS last_post_id,
posts.subject AS subject,
users.id AS user_id,
users.user_name AS user_name,
COUNT(posts.thread_id) - 1 AS replies
FROM threads,
WHERE threads.id = posts.thread_id AND
users.id = posts.user_id
GROUP BY threads.id
ORDER BY threads.updated DESC

Limecat is not pleased.

Last edited by:

fuzzy logic: Feb 26, 2004, 10:29 PM