[web] [SQL] cannot come with a query that does what *I* want

431

Apr 5, 2009 at 5:31 AM

... and what I want is supposed to take precedence over what MySQL wants. Here is the deal: I have two tables, named threads and posts. I simplify them up to the maximum so that they contain only meaningful information. Out of context information is stripped. threads * thread_id (INT, autoincrement, PK) * forum_id (INT) post * post_id (INT, autoincrement, PK) * thread_id (INT) * post_date (DATETIME) Now, I have a query to write: I want to retrieve the last post of each thread for a known forum_id. The best I cam with is:


SELECT * 
FROM 
  threads as t1,
  (SELECT * 
   FROM 
     posts as st1, threads as st2
   WHERE st2.forum_id=1
     AND st1.thread_id = st2.thread_id
   ORDER BY st1.post_id DESC 
   ) as t2
WHERE t1.forum_id=1
  AND t2.forum_id = t1.forum_id
  AND t2.thread_id = t1.thread_id

And this certainly doesn't give me what I want. Suppose that threads and posts contains the following information:


threads
--------------------------
thread_id   |  forum_id
--------------------------
  1         |    1
  2         |    1
  3         |    2
  4         |    1
  5         |    2

posts
---------------------------------------------
post_id   |  thread_id  |  post_date
---------------------------------------------
  1       |    1        | 2009-04-01 12:00:00
  2       |    2        | 2009-04-01 13:00:00
  3       |    2        | 2009-04-01 14:00:00
  4       |    1        | 2009-04-01 15:00:00
  5       |    3        | 2009-04-01 16:00:00
  6       |    1        | 2009-04-01 17:00:00
  7       |    4        | 2009-04-01 18:00:00
  8       |    5        | 2009-04-01 19:00:00
  9       |    5        | 2009-04-01 20:00:00

What I want is (for example, for forum 1)


------------------------------------------------------------
  forum_id  |  post_id   |  thread_id  |  post_date
------------------------------------------------------------
      1     |    6       |    1        | 2009-04-01 17:00:00
      1     |    3       |    2        | 2009-04-01 14:00:00
      1     |    7       |    4        | 2009-04-01 18:00:00

But I seem unable to find a request that does "just" that. Question can someone here help me? Thanks in advance (cookied are distributed accross teh intartube for every answer).

AlphaCoder

Apr 5, 2009 at 5:39 AM

I must be missing something here. But couldn't you just do this:

query the thread table for threads matching your known forum id. then for each thread returned, just query the post table for all posts who's thread_id matches the current thread_id, order by post_date DESC, and print out the first one.

Pretty sure that would do what you're looking for.

Author

431

Apr 5, 2009 at 5:46 AM

Quote:
Original post by AlphaCoder
I must be missing something here. But couldn't you just do this:

query the thread table for threads matching your known forum id. then for each thread returned, just query the post table for all posts who's thread_id matches the current thread_id, order by post_date DESC, and print out the first one.

Pretty sure that would do what you're looking for.

That's practical if you have a small number of thread_id. But if you get a gorwing number of threads, that n+1 requests, and it's going to be slow as hell.

But yes, that would definitely do it, except that it's not feasible in this context :) But thanks anyway.

238

Apr 5, 2009 at 5:58 AM

I must be missing some obvious problem, but with the data set you've provided a plain old inner join seems to give the right results:

SELECT forum_id, thread_id, MAX(post_date) FROM threads   NATURAL JOIN postsWHERE    forum_id = 1 GROUP BY t.thread_id

EDIT: ah, I see you also need the post_id. Back to work.

156

Apr 5, 2009 at 6:07 AM

Have you looked into the GROUP BY statement? and derived tables / subqueries / whatever MySQL calls them?

SELECT forum_id,
post_id,
thread_id,
post_date
FROM threads t
INNER JOIN posts p
ON p.thread_id = t.thread_id
INNER JOIN (
SELECT thread_id,
MAX(post_id) AS LastPostID
FROM posts
GROUP BY thread_id
) x
ON x.thread_id = p.thread_id
AND x.LastPostID = p.post_id
WHERE t.forum_id = 1

Or something along those lines.

The query in parenthesis will give you each thread_id and the highest post_id for each one (I'm assuming that a higher post_id would correlate to a higher date). Then you can join that back to the main posts table and only get those records.

238

Apr 5, 2009 at 6:10 AM

Yep. Ended up with this myself:

SELECT forum_id, post_id, thread_id, post_date   FROM posts        NATURAL JOIN (SELECT forum_id, thread_id, MAX(post_date) AS post_date                        FROM threads                             NATURAL JOIN posts                        WHERE forum_id = 1                    GROUP BY thread_id) s;

Author

431

Apr 5, 2009 at 6:40 AM

You guys are rockstars.

r++ for those who are not already r++ by me :)

Alpha_ProgDes

519

Alpha_ProgDes

Apr 5, 2009 at 8:38 AM

Quote:
Original post by krez
Have you looked into the GROUP BY statement? and derived tables / subqueries / whatever MySQL calls them?
SELECT forum_id, post_id, thread_id, post_dateFROM   threads tINNER JOIN posts p    ON p.thread_id = t.thread_idINNER JOIN (            SELECT thread_id, MAX(post_id) AS LastPostID            FROM   posts            GROUP BY thread_id           ) x   ON x.thread_id = p.thread_id AND x.LastPostID = p.post_idWHERE   t.forum_id = 1
Or something along those lines.

The query in parenthesis will give you each thread_id and the highest post_id for each one (I'm assuming that a higher post_id would correlate to a higher date). Then you can join that back to the main posts table and only get those records.

Easier for me to read that's all. [smile]

Question. Does Toohrvyk's and krez query do the same thing with the same result?

238

Apr 5, 2009 at 9:03 AM

Quote:
Original post by Alpha_ProgDes
Question. Does Toohrvyk's and krez query do the same thing with the same result?

Nope. My solution returns the latest post for every thread in a forum, whereas Krez's solution returns the post with the highest identifier for every thread in a forum.

If having a smaller identifier implies being older, then both versions will be equivalent, with his version being faster (since mine has to perform an inner join on a potentially unindexed date field).

Author

431

Apr 5, 2009 at 3:21 PM

Quote:
Original post by ToohrVyk
Quote:
Original post by Alpha_ProgDes
Question. Does Toohrvyk's and krez query do the same thing with the same result?
Nope. My solution returns the latest post for every thread in a forum, whereas Krez's solution returns the post with the highest identifier for every thread in a forum.

If having a smaller identifier implies being older, then both versions will be equivalent, with his version being faster (since mine has to perform an inner join on a potentially unindexed date field).

post_id begin an auto_increment, having a small post_id means that th post is old - but I tend to prefer logical code over tricky code. And the date is indexed :)

156