Cross referencing problem - mySQL
Posted: Thu Aug 14, 2003 5:39 pm
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:
threads has entries:
then I want to do something like:
SELECT * FROM threads WHERE <clever stuff>;
and get back:
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 CherylCode: Select all
threadID parentboard lpuser startedby
1 5 1 1
2 1 1 2
3 3 3 2SELECT * 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