getting the username not id

[fr8]

How can i get the username to display in this sql query?

SELECT js_forum_topics.topicid, js_forum_topics.topic_name, 
js_forum_topics.topic_views, js_forum_topics.topic_subject,
 count( js_forum_replys.topicid )  AS repcount
FROM js_forum_topics
LEFT  JOIN js_forum_replys ON js_forum_topics.topicid = 
js_forum_replys.topicid
RIGHT  JOIN js_users ON js_forum_topics.topic_starter = 
js_users.userid
GROUP  BY js_forum_topics.topicid

CREATE TABLE js_forum (
forumid int(11) NOT NULL auto_increment,
forum_name varchar(255) NOT NULL default '',
forum_subject varchar(255) NOT NULL default '',
UNIQUE KEY forumid (forumid)
)

CREATE TABLE js_forum_replys (
replyid int(11) NOT NULL auto_increment,
forumid int(11) NOT NULL default '0',
topicid int(11) NOT NULL default '0',
reply_subject varchar(255) NOT NULL default '',
reply_comment text NOT NULL,
reply_date datetime NOT NULL default '0000-00-00 00:00:00',
reply_user int(11) NOT NULL default '0',
UNIQUE KEY replyid (replyid)
)

CREATE TABLE js_forum_topics (
topicid int(11) NOT NULL auto_increment,
forumid int(11) NOT NULL default '0',
topic_name varchar(255) NOT NULL default '',
topic_subject varchar(255) NOT NULL default '',
topic_starter varchar(15) NOT NULL default '',
topic_message text NOT NULL,
topic_date datetime NOT NULL default '0000-00-00 00:00:00',
topic_views int(11) NOT NULL default '0',
lastpost_user int(11) default NULL,
lastpost_time datetime default NULL,
UNIQUE KEY topicid (topicid)
)

[tomhath]

You can't with the tables you have. You'll need to join with the table that stores the name (the id should get you there)

[fr8]

join how? with another JOIN in my statement or physicaly by making the topic_stater the name instead of the integer?

[fr8]

Originally posted by tomhath
You can't with the tables you have. You'll need to join with the table that stores the name (the id should get you there)

Isnt that what this is doing in the original query?

RIGHT  JOIN js_users ON js_forum_topics.topic_starter =  

js_users.userid 

[fr8]

I got this to get the username but all of my reply counts (repcount) get set to zero. Any ideas?

SELECT js_forum_topics.topicid, js_forum_topics.topic_name,
 js_forum_topics.topic_subject, js_users.username, 
js_forum_topics.topic_views, count( js_forum_replys.topicid )  AS 
repcount
FROM js_forum_topics
LEFT  JOIN js_forum_replys ON js_forum_topics.topicid = 
js_forum_replys.topicid
RIGHT  JOIN js_users ON js_forum_topics.topic_starter = js_users.username
WHERE js_forum_topics.topic_starter = js_users.userid
GROUP  BY js_forum_topics.topicid