CODEWITHSUNDEEP
mysql
sark9012
sark9012

Reputation: 5737

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 LIMIT 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!

Upvotes: 0

Views: 43

Answers (3)

Neil
Neil

Reputation: 5780

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.

Upvotes: 0

Yahia
Yahia

Reputation: 70369

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)...

Upvotes: 0

feeela
feeela

Reputation: 29922

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

Upvotes: 1

Related Questions