SQL Help

grahamor

Hi,

I have a posts table and a replies table. I want to be able to count the number of replies that relate to each post and output them on the front end....

Table Posts
post_id
title
body

Table Replies
reply_id
name
email
post_id

So the tables are linked with post_id.

When i am displaying all the active posts on the front end i do

SELECT title,body FROM Posts

How can i count the number of rows in Replies that relate to the current row being selected.

I was thinking of some table variable but i think im making it really complicated for myself.

Thanks for any advice

tiptap

select post_id, count(post_id) from tblReplies
group by post_id

Will that work for you,
you will know how many entries you have for each post

dazberry

Something like this might do it for you... wouldn't be a top SQL person myself...

select p.post_id, count(r.*) from posts p, replies r
where p.post_id = r.post_id
group by 1
union
select p.post_id, 0 from posts p
left outer join replies r on (r.post_id = p.post_id)
where r.post_id is null

D.

Beano

When posting questions asking for help on SQL you should really say what flavour of SQL you are using.

select p.post_id, count(distinct r.reply_id)
from posts p
left join replies r
on p.post_id = r.post_id

should do the trick for you.

grahamor

Thanks alot guys for the informative replies.

I knew it was easier than the way i was planning on doing it.