Page 1 of 1

Cross referencing problem - mySQL

Posted: Thu Aug 14, 2003 5:39 pm
by RFairey
Say I have two tables - users, and threads, in a mySQL db for a forum (I actually do!).
table users: ID, username, <other fields>
table thread: threadID, parentboard, lpuser, startedby, <other fields>

lpuser is the ID of the user who posted last, started by is the ID of the user who started the thread.

How do I write ONE sql query, using joins or otherwise, to return any number of rows of the threads table, with the userID numbers replaced by their corresponding username?

eg:
users has entries:

Code: Select all

ID   username
1     Alice
2     Bob
3     Cheryl
threads has entries:

Code: Select all

threadID   parentboard    lpuser  startedby
1            5              1           1
2            1              1           2
3            3              3           2
then I want to do something like:
SELECT * FROM threads WHERE <clever stuff>;

and get back:

Code: Select all

threadID   parentboard    lpuser  startedby
1               5         Alice        Alice
2               1         Alice        Bob
3               3         Cheryl       Bob

Posted: Fri Aug 15, 2003 5:28 am
by tylerdurden
This should do it:

Code: Select all

SELECT *,users.username as lpuser, u2.username as startedby FROM `thread` left join users on (thread.lpuser = users.id) left join users as u2 on (thread.startedby=u2.id)

Posted: Fri Aug 15, 2003 9:09 am
by RFairey
8O
Yipe! Erm - can anyone explain this?

Ah - I think I see.....

If I wanted to cross reference to the users table three or four times, would I have to make up more names like u2?

Posted: Fri Aug 15, 2003 9:33 am
by tylerdurden
Yep.