JOIN Help.

[EKINdesigns]

Alright...After a few weeks I was able to figure out a lot of the query.  I have only one thing left.

 

I will re-explain when I am trying to accomplish:

 

I have 3 tables: forums, topics and users.  I need to select all the forums ordered by ID, I also need a topic count for that forum, and the most recent topic with the user information.

 

Currently I get everything including topic but it isnt the most recent.

 

This is what I have:

 

$query = "SELECT `forums`.{$select}

, COUNT(`topics`.`id`) AS `topic_count`

, `topics`.`id` AS `recent_topic_id`

, `topics`.`title` AS `recent_topic_title`

, `topics`.`date` AS `recent_topic_date`

, `users`.`id` AS `recent_topic_poster_id`

, `users`.`username` AS `recent_topic_poster`

  FROM  `forums`

  LEFT

JOIN `topics`

  ON `topics`.`fid`

  = `forums`.`id`

  LEFT

JOIN `users`

  ON `users`.`id`

  = `topics`.`poster`

  WHERE

  `forums`.`cid`='{$id}'

  GROUP BY

  `forums`.`id`

  ORDER BY

  `forums`.`id`";

[aschk]

Without working with data i'm got the following for you to try

 

SELECT f.name
      , x.*
      , u.*
FROM forums f
JOIN 
  (SELECT t.fid, COUNT(*) as count
   FROM topics t
   GROUP BY t.fid
  ) tc ON tc.fid = f.id
JOIN
( SELECT * FROM (
    SELECT id,MAX(`date`) as 'max' FROM topics t
    GROUP BY t.id
  ) tm
  JOIN
  (
    SELECT t.fid, t.id, t.title, t.date FROM topics t
  ) ta ON ta.max = tm.date
) x ON x.fid = f.id
JOIN
users u ON u.id = x.poster