trouble with JOINs in mysql

[fr8]

Ive been working on this for a while in another thread but was told that i should finish it in here.

Here is the link for the background information on the problem.
Also contains my database structure info.
http://www.phpbuilder.com/board/showthread.php?s=&threadid=10258086

SELECT js_forum_topics.topicid, js_forum_topics.topic_name, 
  js_forum_topics.topic_views, js_forum_topics.topic_subject, 
  js_users.username, count(*) 
 FROM  js_forum_topics INNER JOIN js_users 
    LEFT OUTER JOIN js_forum_replys  

   ON  js_forum_topics.topicid = js_forum_replys.topicid  

   ON  js_forum_topics.starter = js_users.userid 
 GROUP BY js_forum_topics.topicid

//MySQL said: 
//You have an error in your SQL syntax near 'LEFT OUTER JOIN 
//js_forum_replys ON js_forum_topics.topicid = js_forum_replys.t' at line 4

[bubblenut]

I think the syntax is as follows in MySQL

SELECT js_forum_topics.topicid, js_forum_topics.topic_name,  

  js_forum_topics.topic_views, js_forum_topics.topic_subject,  

  js_users.username, count(*)  

 FROM  js_forum_topics INNER JOIN js_users  

    LEFT OUTER JOIN js_forum_replys  ON (js_forum_topics.topicid = js_forum_replys.topicid  && js_forum_topics.starter = js_users.userid)
 GROUP BY js_forum_topics.topicid 

[Lars_Berg]

SELECT js_forum_topics.topicid, js_forum_topics.topic_name, 
  js_forum_topics.topic_views, js_forum_topics.topic_subject, 
  js_users.username, count(*) 
 FROM  js_forum_topics INNER JOIN js_users 
ON  js_forum_topics.topicid = js_forum_replys.topicid  

    LEFT OUTER JOIN js_forum_replys  

      ON  js_forum_topics.starter = js_users.userid 
 GROUP BY js_forum_topics.topicid 

When doing a group by all non-agregated columns in the select-list should be present in the group by clause, otherwise you will get unpredicatable results.

[fr8]

Originally posted by Lars Berg

SELECT js_forum_topics.topicid, js_forum_topics.topic_name, 
  js_forum_topics.topic_views, js_forum_topics.topic_subject, 
  js_users.username, count(*) 
 FROM  js_forum_topics INNER JOIN js_users 
ON  js_forum_topics.topicid = js_forum_replys.topicid  

    LEFT OUTER JOIN js_forum_replys  

      ON  js_forum_topics.starter = js_users.userid 
 GROUP BY js_forum_topics.topicid 

When doing a group by all non-agregated columns in the select-list should be present in the group by clause, otherwise you will get unpredicatable results. [/B]

That does work. But it will only display topics that have replys to them. Any new topic that is made wont be displayed because no one has replied to it. Any idea on how to do bring those to the list?

[fr8]

Should i just create a new field in the topics and just update that with each new reply?
I dont want to do this but if thats the only way then I guess i will.

[fr8]

anyone know how to list the other topics that do not have any replys?