Andee ([info]kjpepper) wrote in [info]php_mysql,
@ 2007-04-17 15:34:00
Previous Entry  Add to memories!  Tell a Friend!  Next Entry
Current mood: busy

trying to narrow down my results a bit...

Hi! first time posting here. been using mysql casually for a while but only now learning the true complexities of it and databases in general.

So I have this problem: As a practice project I've cobbled together a very primative message board type thing, fed from a table called wow_posts and another from wow_comment. (yes it's for a WoW guild web site, you can laugh at my nerditude later.) The first table holds the actual posts themselves, the second hold replies.

I have the following query:

SELECT wow_comment.postdate, wow_comment.user, wow_posts.bid, wow_posts.category, wow_posts.subject from wow_comment LEFT JOIN wow_posts on wow_comment.bid=wow_posts.bid UNION SELECT postdate, user, bid, category, subject from wow_posts ORDER BY postdate DESC Limit 10

which returns this:

postdate user bid category subject
2007-04-17 13:29:36 5 57 RQ Leather/tailor people
2007-04-17 12:06:11 1 57 RQ Leather/tailor people
2007-04-17 08:50:20 3 57 RQ Leather/tailor people
2007-04-17 07:38:00 5 57 RQ Leather/tailor people
2007-04-17 06:02:33 1 57 RQ Leather/tailor people
2007-04-16 22:37:09 5 57 RQ Leather/tailor people
2007-04-13 23:04:21 5 56 SS Secondary skillz
2007-04-11 17:10:04 8 55 AN 1t's j0ur b1r7hd4i!!!!!
2007-04-11 10:44:57 5 55 AN 1t's j0ur b1r7hd4i!!!!!
2007-04-11 10:23:55 1 55 AN 1t's j0ur b1r7hd4i!!!!!

my question is there a way to tweak this query, either by subquerying, distinct, group by or what have you so that instead of the above (which is the last 10 posts and replies to the board) it returns the last 10 threads that were posted into only? (ie, make bid distinct but retain the rest of the fields so it would return only the latest posts for each one?)

any help appreciated.


(Post a new comment)


[info]powerlord
2007-04-17 08:53 pm UTC (link)
I'm not really sure what the purpose of the union is, but I'll try to do what I think you want...

SELECT mc.timestamp, p.bid, p.category, p.subject
FROM wow_posts p
LEFT JOIN (
  SELECT bid, MAX(timestamp)
  FROM wow_comments
  GROUP BY bid) mc
ON p.bid = mc.bid
ORDER BY p.timestamp DESC
LIMIT 10


Of course, this doesn't give you which user made the last post. To get that, you'll need yet another join.

SELECT mc.timestamp, c.user, p.bid, p.category, p.subject
FROM wow_posts p
LEFT JOIN (
  SELECT bid, MAX(timestamp)
  FROM wow_comments
  GROUP BY bid) mc
ON p.bid = mc.bid
LEFT JOIN wow_comments c
ON mc.bid = c.bid AND mc.timestamp = c.timestamp
ORDER BY p.timestamp DESC
LIMIT 10


P.S. There is probably an easier way to write the subquery as a correlated subquery, but correlated subqueries are inefficient and likely to be slow.

(Reply to this)

Create an Account
Forgot your login or password?
Login w/ OpenID
English • Español • Deutsch • Русский…