William William - 7 months ago 9
SQL Question

Mysql Query Join Table on the most recent row

I have a page of categories, they worked fine until I did a join.

The categories display like so:


category 1-----------------------0----------0

discussion 1 by someone

category 2-----------------------0----------0

discussion 2 by someoneelse


Now where it says discussion, I need to display the last discussion posted to that category based on it's
discussion_id
. I have tried
ORDER BY ... DESC
but it sorts by category names not the discussion names and posted by.

$sql = "SELECT *, COUNT(d.cat_id) as count
FROM discussions as d
LEFT JOIN categories c ON (c.cat_id = d.cat_id)
RIGHT JOIN soldiers s ON (s.uid = d.discussion_poster)
GROUP BY d.cat_id";
$result = query($sql);


while (($row = mysqli_fetch_assoc($result)) != false) {
$cat_id = $row['cat_id'];
$discussion_id = $row['discussion_id'];
$cat_title = $row['cat_title'];
$discussion_title = $row['discussion_title'];
$discussion_time = $row['discussion_time'];
$count = $row['count'];
$discussion_poster_id = $row['discussion_poster'];
$discussion_poster = $row['soldier'];
}

Answer

You need to use a LEFT OUTER JOIN

$sql = "
SELECT *
FROM categories c
INNER JOIN
(
   SELECT MAX(discussion_id) discussion_id,
   COUNT(discussion_id) as count,
   cat_id
   FROM
   discussions
   GROUP BY
   cat_id
) as d1
ON (c.cat_id = d1.cat_id)
INNER JOIN discussions as d
ON (d1.discussion_id = d.discussion_id)
INNER JOIN soldiers s
ON (s.uid = d.discussion_poster)
GROUP BY d.cat_id";