SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    May 2006
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select all rows greater than... (multiple tables)

    Hi,

    Edit: Using MySQL 4.0.27.

    I have a standard forum database, and am trying to extract data from it. I want to extract "Last 7 topics you have posted in", and need some SQL help!

    The data returned should list the last 7 topics you have posted in. The last topic you have posted in will be at the top.

    A topic row can have many associated post rows (many posts in one topic).

    This SQL query works for this purpose: (Assume userid = 2)

    Code:
    SELECT t.*
    FROM topics t, posts p
    WHERE p.topic_id = t.topic_id
    AND p.poster_id = 2
    GROUP BY t.topic_id
    ORDER BY p.post_time DESC
    LIMIT 7
    Where table topics has info on each topic (thread), such as start date, last poster, etc.
    Table posts has info on each post: which topic does it belong to, poster, posting time, etc.

    The problem:
    There is extra functionality that I can't get working! In the same query, I want to count the number of posts made to each of these topics since your last post in each of the topics. So the user will be able to tell, "ah, since my last post there have been 2 replies to that topic".

    Here is my attempt at the SQL, but it doesn't work as intended:

    Code:
    SELECT t.*, count(p2.post_id)
    FROM topics t, posts p, posts p2
    WHERE p.topic_id = t.topic_id
    AND p.poster_id = 2
    AND p2.topic_id = p.topic_id
    AND p2.post_time > max(p.post_time)
    GROUP BY t.topic_id
    ORDER BY p.post_time DESC
    LIMIT 7
    Using something like "HAVING p2.post_time > max(p.post_time)" instead of the similar WHERE statement hasn't helped either. Any help in re-writing that query for the intended functionality would be much appreciated.

    Regards,
    Eoin

  2. #2
    SitePoint Member
    Join Date
    May 2006
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This code is now working as intended, if anybody's interested:

    Code:
    select a.topic_id, t.topic_title, count(p.post_id)-1 as cnt_replies, a.lastpost, p2.post_time
    from (
    select topic_id, max(post_id) lastpost
    from phpbb_posts
    where poster_id = 2
    group by topic_id
    order by lastpost desc
    limit 5) a,  phpbb_posts p, phpbb_posts p2, phpbb_topics t
    where a.topic_id = p.topic_id
    and p.post_id >= a.lastpost
    and t.topic_id = a.topic_id
    and p2.post_id = a.lastpost
    group by a.topic_id
    order by p.post_id desc


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •