Gathering multiple fields

$q = "SELECT s.id, s.title, s.description,
      (SELECT COUNT(*) FROM ".FORUM_THREADS." t WHERE t.cat_id = s.id) AS topics,
      (SELECT COUNT(*) FROM ".FORUM_REPLIES." r INNER JOIN ".FORUM_THREADS." t ON r.thread_id = t.id 
      WHERE t.cat_id = s.id) AS replies,
      (SELECT r.date FROM ".FORUM_REPLIES." r INNER JOIN ".FORUM_THREADS." t ON r.thread_id = t.id 
      WHERE t.cat_id = s.id ORDER BY r.date DESC LIMIT 1) AS last_post
      FROM ".FORUM_SUBCATEGORIES." s WHERE s.parent = '$catid' AND s.status = '0' ORDER BY s.id";

I am attempting to select more than one field on the following part of the query

(SELECT r.date FROM ".FORUM_REPLIES." r INNER JOIN ".FORUM_THREADS." t ON r.thread_id = t.id 
INNER JOIN ".TBL_USERS." u ON u.id = r.author WHERE t.cat_id = s.id ORDER BY r.date DESC LIMI开发者_运维知识库T 1) AS last_post

Along with r.date, I want to select u.username and r.author.

How can I go about doing this?

Thanks!

Just add them to the SELECT:

(SELECT r.date, r.author, u.username FROM ".FORUM_REPLIES." r INNER JOIN ".FORUM_THREADS." t ON r.thread_id = t.id 
INNER JOIN ".TBL_USERS." u ON u.id = r.author WHERE t.cat_id = s.id ORDER BY r.date DESC LIMIT 1) AS last_post

UPDATED after comment from OP:

You need to do 3 separate selects OR (depending on your data model) change the query so that the last_post query ends up after/in the FROM clause (there it can have as many columns as you want)...

Luke, you have a central select statement which uses nested select statements for getting the count. You can't depend on the nested select statements to count as the inner join, so you're going to have to add them to the central select statement instead.

In other words, join ".FORUM_REPLIES." and "u" (not sure what that's supposed to represent) with ".FORUM_SUBCATEGORIES.". I'd write the query for you, but I don't know how to link subcategories with replies and subcategories with u.