Help with this JOIN

Question

$threads = mysql_query("SELECT DISTINCT t.id, t.title
                        FROM threads t LEFT JOIN comments c ON t.id = c.parentID
                        ORDER BY c.date DESC");

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

 echo "<li><a href=\"?threadID=$thread[id]\">".htmlspecialchars($thread['title'])."</a></li>\n";

}

Can anyone see a problem in this code? It wont work as i want it. I want it the thread that was commented in latest on top, and the the next latest etc.

echo $thread[parentID] echoes nothing

comments:
id, comment, parentID, date

Answer 1

Try running the query in MySQL Query browser on your data, and see if this returns any results. If it does, then the issue is with your echo, not your query.

Answer 2

You are ordering by a column not in your DISTINCT list.

This is valid syntax in MySQL, however this ORDER BY is meaningless.

This syntax is a MySQL extension, this query will fail in any other RDBMS engine of the big four.

It is used to simplify the DISTINCT and GROUP BY queries in the cases when same value of column in SELECT or ORDER BY always corresponds to same value of column in GROUP BY or DISTINCT.

If your case, you can have multiple values of c.date for each value of t.id, and which value of c.date will be selected for ORDER BY is far from being guaranteed (it can be the last value, the first value or any other value).

Rewrite your query as this:

SELECT  t.id, t.title,
        (
        SELECT  c.date
        FROM    comments c
        WHERE   c.parent_id = t.id
        ORDER BY
                c.date DESC
        LIMIT 1
        ) lastcomment
FROM    threads t
ORDER BY
        ISNULL(lastcomment), lastcomment

Answer 3

i'm not sure exactly what you are trying to achieve, but if you want to echo the parentID, you need to include it in the query.

$threads = mysql_query("SELECT DISTINCT t.id, t.title, c.parentID
                        FROM threads t LEFT JOIN comments c ON t.id = c.parentID
                        ORDER BY c.date DESC");

Answer 4

I haven't used MySQL in awhile so this is something I know works in tSQL. You could try:

SELECT DISTINCT t.id, t.title
FROM threads t LEFT JOIN comments c ON t.id = c.parentID AND 
c.id = (SELECT MAX(c.id) FROM comments where comments.parentID = t.id)
ORDER BY c.date DESC

This could easily get a bit slow with a lot of rows in comments. It might make some sense to store the last comment date in threads if you can if you notice a performance problem.

Answer 5

I tried this and it worked as what it sounds like you want

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

The only real thing that I changed was using

FROM threads AS T