multiple join maybe?

[dodo]

hi, i'm a newbie comes to join statement for mysql. could someone help me out?

here's my table structures

CREATE TABLE dmb_threads (
tid mediumint(10) unsigned NOT NULL auto_increment,
fid tinyint(3) unsigned NOT NULL default '0',
subject varchar(30) NOT NULL default '',
started int(10) unsigned NOT NULL default '0',
icon varchar(20) NOT NULL default '',
views int(100) unsigned NOT NULL default '0',
replies int(100) unsigned NOT NULL default '0',
authorid smallint(6) unsigned NOT NULL default '0',
closed enum('0','1') NOT NULL default '0',
topped enum('0','1') NOT NULL default '0',
mark enum('none','save') NOT NULL default 'none',
privacy enum('public','Member','Star Member','Moderator','Super Moderator','Administrator') NOT NULL default 'public',
haspoll enum('0','1') NOT NULL default '0',
paid enum('0','1') NOT NULL default '0',
lastpostuserid smallint(6) unsigned NOT NULL default '0',
lastpostdesc varchar(255) NOT NULL default '',
PRIMARY KEY (tid),
KEY fid (fid)
) TYPE=MyISAM;

table 2

CREATE TABLE dmb_members (
uid smallint(10) unsigned NOT NULL auto_increment,
username varchar(10) NOT NULL default '',
password varchar(32) NOT NULL default '',
status enum('Member','Star Member','Moderator','Super Moderator','Administrator','Banned') NOT NULL default 'Member',
regdate int(10) unsigned NOT NULL default '0',
regip varchar(40) NOT NULL default '',
reghost varchar(100) NOT NULL default '',
lastvisit int(10) unsigned default '0',
attachmentbytes bigint(20) unsigned NOT NULL default '0',
money decimal(10,2) unsigned NOT NULL default '0.00',
PRIMARY KEY (uid),
UNIQUE KEY regip (regip),
KEY username (username),
KEY status (status)
) TYPE=MyISAM;

i know there's a way to select the username from dmb_members with the corresponding authorid.

i will do something like:

SELECT m.username as author, t.* FROM dmb_members m JOIN dmb_threads t ON (m.uid = t.authorid) LIMIT 1

but now the threads table also contain an id for last posted user.

how can i find out both of their usernames by using one query?

thanks!

[dodo]

well i guess i don't need to use join. just select the table and the field twice.

looked at phpbb code

$sql = "SELECT u.username AS username_1, u.user_id AS user_id_1, u2.username AS username_2, u2.user_id AS user_id_2, u.user_sig_bbcode_uid, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_regdate, u.user_msnm, u.user_viewemail, u.user_rank, u.user_sig, u.user_avatar, pm.*, pmt.privmsgs_bbcode_uid, pmt.privmsgs_text
FROM " . PRIVMSGS_TABLE . " pm, " . PRIVMSGS_TEXT_TABLE . " pmt, " . USERS_TABLE . " u, " . USERS_TABLE . " u2
WHERE pm.privmsgs_id = $privmsgs_id
AND pmt.privmsgs_text_id = pm.privmsgs_id
$pm_sql_user
AND u.user_id = pm.privmsgs_from_userid
AND u2.user_id = pm.privmsgs_to_userid";