Any mysql wizards here?

[eluser]Sarre[/eluser]
I was playing around with the CXForum code (http://ellislab.com/forums/viewthread/91409/), and got stuck on a query. So here's a little mysql challenge (well, it's a challenge for me at least )

Suppose I want to collect data from three tables (only showing relevant columns):

Code:

Table USERS
-----+-----------
| id | username |
-----+-----------
| 1  | Admin    |
-----+-----------
| 2  | Me       |
-----------------

Table FORUM_TOPICS
-----+----------+---------+-------+------+--------+------+----------
| id | forum_id | user_id | title | date | status | view | replies |
-----+----------+---------+-------+------+--------+------+----------
| 1  | 2        | 1       | blah  | 7    | 1      | 24   | 0       |
| 2  | 2        | 2       | blah! | 8    | 1      | 54   | 2       |
--------------------------------------------------------------------

Table POSTS
-----+----------+---------+-------+------+---------
| id | topic_id | user_id | title | date | status |
-----+----------+---------+-------+------+---------
| 1  | 2        | 2       | re:   | 10   | 1      |
| 2  | 2        | 1       | re:   | 11   | 1      |
---------------------------------------------------

I want to give an overview of forum 2, which has 2 topics, one of which has 2 replies. The overview should be:

Code:

--------+--------+---------+-------+--------------+------------------
| Title | author | replies | views | last_replier | last_reply_date |
--------+--------+---------+-------+--------------+------------------
| blah! | Me     | 2       | 54    | Admin        | 11              |
| blah  | Admin  | 0       | 24    | null         | null            |
---------------------------------------------------------------------

I got this far:

Code:

SELECT t.*, u.username as user FROM forum_topics t JOIN users u ON t.user_id = u.id
WHERE forum_id = 2 AND t.status = 1 ORDER BY date DESC LIMIT 10

How do I change this query so that last_replier and last_reply_date are retrieved correctly as well? (Is it even possible to do this in one query?)

Thanks!

[eluser]davidbehler[/eluser]

Code:

SELECT
   t.*,
   u.username AS user,
   (SELECT u2.username FROM post p JOIN users u2 ON p.user_id = u2.user_id WHERE p.topic_id = t.topic_id ORDER BY p.date desc LIMIT 0,1) AS last_replier,
   (SELECT max(p.date) FROM post p JOIN users u2 ON p.user_id = u2.user_id WHERE p.topic_id = t.topic_id) AS last_reply_date
FROM
   forum_topics t JOIN users u ON t.user_id = u.id
WHERE
   forum_id = 2 AND t.status = 1 ORDER BY date DESC LIMIT 10

It's untested, but I guess that might work.

[eluser]Sarre[/eluser]
Thanks for the quick response!

Unfortunately, I'm getting an error:

Error Number: 1054

Unknown column 't.topic_id' in 'where clause'

SELECT t.*, u.username AS user, (SELECT u2.username FROM forum_posts p JOIN users u2 ON p.user_id = u2.user_id WHERE p.topic_id = t.topic_id ORDER BY p.date desc LIMIT 0,1) AS last_replier, (SELECT max(p.date) FROM forum_posts p JOIN users u2 ON p.user_id = u2.user_id WHERE p.topic_id = t.topic_id) AS last_reply_date FROM forum_topics t JOIN users u ON t.user_id = u.id WHERE forum_id = 2 AND t.status = 1 ORDER BY date DESC LIMIT 10

I'm looking into it...

[eluser]davidbehler[/eluser]
try changing

Code:

t.topic_id

to

Code:

t.id

[eluser]Sarre[/eluser]
Did I mention you are brilliant?

Thanks!!! :-)

P.S. In case someone else uses this, I also had to change u2.user_id to u2.id etc...