How should I join these tables?

Question

These are the tables:

threads:
id, date, title, text


comments:
id, thread_id, date, comment

How would I do to list the last commented thread on top?

This is currently how it looks:

$threads = mysql_query("SELECT id, title FROM threads ORDER BY date ASC");

while ($thread = mysql_fetch_assoc($threads)) {

 echo $thread['title'];

}

I can't figure this one out folks. So if someone could give me an hand that would be great!

Cheers!

Answer 1

Try this:

SELECT DISTINCT t.id, t.title
  FROM threads t LEFT JOIN comments c ON t.id = c.thread_id
 ORDER BY c.date DESC

Left join is needed in case you have threads with no comments.

Answer 2

select * from Threads t
    inner join Comments c
        on t.id = c.Thread_id
order by c.date

Answer 3

Since you wanted a list of threads with the latest comments on top, you'd have to join by id and then sort DESC by comment date.

SELECT t.id, t.title, c.date
FROM threads t, comments c
WHERE t.id = c.thread_id
ORDER BY c.date DESC

Thanks

Answer 4

This one should get it done:

SELECT threads.id, threads.title, max(comments.date)
   FROM threads 
    LEFT OUTER JOIN comments
   ON threads.id = comments.thread_id
GROUP BY threads.id, threads.title
ORDER BY max(comments.date) DESC