Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.
Hi all, please see this major site announcement: https://www.boards.ie/discussion/2058427594/boards-ie-2026

SQL Help

  • 05-07-2008 03:54PM
    #1
    grahamor
    Registered Users, Registered Users 2 Posts: 872 ✭✭✭


    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


Comments

  • #2
    tiptap
    Closed Accounts Posts: 317 ✭✭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


  • #3
    dazberry
    Registered Users, Registered Users 2 Posts: 2,157 ✭✭✭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.


  • #4
    Beano
    Closed Accounts Posts: 3,357 ✭✭✭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.


  • #5
    grahamor
    Registered Users, Registered Users 2 Posts: 872 ✭✭✭grahamor


    Thanks alot guys for the informative replies.

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


Advertisement