Cross referencing problem - mySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
RFairey
Forum Commoner
Posts: 52
Joined: Fri Jun 06, 2003 5:23 pm

Cross referencing problem - mySQL

Post 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
tylerdurden
Forum Commoner
Posts: 66
Joined: Mon Jul 28, 2003 11:52 am
Location: Austria

Post 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)
RFairey
Forum Commoner
Posts: 52
Joined: Fri Jun 06, 2003 5:23 pm

Post 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?
tylerdurden
Forum Commoner
Posts: 66
Joined: Mon Jul 28, 2003 11:52 am
Location: Austria

Post by tylerdurden »

Yep.
Post Reply