table joins

[doddsey_65]

I have the following query which selects all the info i need for the current page and displays it.

 

$topic_info_query = $db->query("SELECT 
								f.forum_id, f.forum_name,
								m.user_id, m.user_username, m.user_group,
								t.thread_topic_id, t.topic_name, t.topic_poster, t.topic_time_posted, t.topic_views,
								t.topic_replies, t.topic_last_poster, t.topic_last_post_time, t.topic_locked,
								t.topic_sticky, t.topic_edited

								FROM ".DB_PREFIX."topics as t

								LEFT JOIN ".DB_PREFIX."members as m
								ON t.topic_poster = m.user_username

								LEFT JOIN ".DB_PREFIX."forums as f
								ON t.forum_id = f.forum_id

								WHERE t.forum_id = '$forum_id'

								ORDER BY t.topic_last_post_time DESC")
								or trigger_error("SQL", E_USER_ERROR);

$topic_num_rows = mysql_num_rows($topic_info_query);

while ($topic_info = mysql_fetch_object($topic_info_query)) 

 

you can see the code working here: http://thevault.cz.cc/index.php?forum=4

 

However the last post section doesnt work.

This is because the LEFT JOIN which joins the members table is on the topic_starter.

but i cant add another join for the topic_last_poster cos they would go to the same thing user_username which wouldnt work.

 

so how do i retain the join for the topic_starter while somehow getting the last_poster to work?

 

I was under the assumption that i may have to create a new db table with seperate user information in but im not sure.

 

[fenway]

Sorry, I don't follow.

[Barand]

Join again to members table but with a different alias

 

LEFT JOIN ".DB_PREFIX."members as m ON t.topic_poster = m.user_username
LEFT JOIN ".DB_PREFIX."members as m2 ON t.topic_starter = m2.user_username

[doddsey_65]

thanks for the reply, i didnt know i could join the same table twice. Ill try it now.