Web Analytics Made Easy -
StatCounter SQL question on a join issue (can't use a subquery) - CodingForum

Announcement

Collapse
No announcement yet.

SQL question on a join issue (can't use a subquery)

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL question on a join issue (can't use a subquery)

    My stupid host is using MySQL 4.0. I will be changing hosts, but for now I would really like to get this query working. I would rather not split the query into two queries because of the pagination class I am using.

    This is a simple message board. I want to select and list threads in the order of the timestamp on the most recent post in each thread (descending). With a subquery I could do it like this:

    Code:
    SELECT t.title_ds,
    t.author_id,
    t.thread_id,
    date_format(t.created_ts, '%%b %%c %%Y, %%l:%%i %%p') as created_ts_f,
    t.status_cd,
    t.views_nr,
    post.created_ts as post_ts
    FROM mb_thread_tbl as t,
    mb_post_tbl as post
    WHERE t.category_id = {$catID}
    AND t.status_cd in ('ACTIVE', 'LOCKED')
    AND post.post_id = (SELECT post_id FROM mb_post_tbl as post2 WHERE post2.thread_id = t.thread_id ORDER BY created_ts desc LIMIT 1)
    ORDER BY post_ts desc
    LIMIT {$pager->get_limit()}
    I can't for the life of me figure out how to do this without the subquery. Does anyone know of a clever method to extract that most recent post's timestamp (and order the results by it) within this query?

  • #2
    Writing the problem down seems to have helped! I have tentatively figured out a solution for this. I added a GROUP BY clause on the post's timestamp and then sort on max(timestamp) like this:

    Code:
    SELECTt.title_ds,
    t.author_id,
    t.thread_id,
    date_format(t.created_ts, '%b %e %Y, %l:%i %p') as created_ts_f,
    t.status_cd,
    t.views_nr,
    [B]max(post.created_ts) as post_ts,[/B]
    FROM mb_thread_tbl as t,
    mb_post_tbl as post
    WHERE t.category_id = {$catID}
    AND t.status_cd in ('ACTIVE', 'LOCKED')
    AND t.author_id = p.person_id
    AND t.thread_id = post.thread_id
    [B]GROUP BY post.thread_id[/B]
    [B]ORDER BY post_ts desc[/B]
    LIMIT {$pager-get_limit()}
    I do wonder if this is a mis-use of the GROUP BY clause; I know sometimes GROUP BY seems to solve an issue but it causes issues as well... for now I'll roll with it and test it a lot.

    Comment

    Working...
    X