Problem with query

Eiolon · #1 (**permalink**) 11-27-07, 13:43

I made a forum. All works well but with one exception. If a reply has not been made to a topic, the topic does not appear on the list of topics.

Here is the query I am using:

Code:

SELECT t.*, r.*, DATE_FORMAT(t.topic_last_reply, '%c/%e/%Y %l:%i %p') AS convdate FROM topics t JOIN replies r ON (t.topic_id = r.reply_topic) WHERE t.topic_forum = ".$_GET['forum_id']." GROUP BY t.topic_subject ORDER BY t.topic_last_reply DESC

When a reply is made, it increments the value in topic_num_replies +1. So what I did is add a HAVING clause:

Code:

SELECT t.*, r.*, DATE_FORMAT(t.topic_last_reply, '%c/%e/%Y %l:%i %p') AS convdate FROM topics t JOIN replies r ON (t.topic_id = r.reply_topic) WHERE t.topic_forum = ".$_GET['forum_id']." GROUP BY t.topic_subject HAVING t.topic_num_replies >= 0 ORDER BY t.topic_last_reply DESC

I get the same result. As soon as I make a reply to the topic it appears on the list.

Thanks for your advice!

gvee · #2 (**permalink**) 11-27-07, 13:59

You want to change your JOIN from an INNER to a LEFT on the replies table.
The reason the second query didn;t erturn aresult is because when there are no replies then the count is NULL, not zero

Eiolon · #3 (**permalink**) 11-27-07, 15:00

Thanks for your reply.

I made it a LEFT join but the results are still the same. I am running this in MySQL directly to make sure no PHP is getting in the way.

Thanks for your help.