3 way join

[xjermx]

I have a "users" table with data like userID, name, email, etc.

 

A "Mailmessage" table with the fields 'ID' (PK, auto), "messagetext", "from_user" (FK, relates back to the userID field in 'users'), "timestamp".

 

And a third table, "Destination", which has "whichmessage" (FK, relates back to the ID field in Mailmessage), "touser" (another FK, relates again to the userID field in users).

 

I'm a newbie, and I think I'm looking at a three way join here, so be gentle, please

 

I'm using dreamhost for my environment, which I believe uses mysql 5.1 or 5.2, I'd have to check.

 

My question is simply:  can someone tell me how they'd format the SQL query to do a join on these tables?  I'm getting lost in reams of info on inner joins and left joins and such. 

 

If it helps, the information that I'm eventually wanting to display back to the program is (pardon my shorthand):

 

Username From,  Username To,  Timestamp, Message Text.

[fenway]

What working queries to do you have so far/

[xjermx]

I have a join currently between my two mail tables.

 

SELECT mailmessage.text, mailmessage.from_user, mailmessage.timestamp, destination.touser

FROM destination

INNER JOIN mailmessage

ON destination.whichmessage=mailmessage.ID_mailtext

ORDER BY mailmessage.timestamp

 

this does return good results, but instead of giving me user names, it gives me user ID numbers.

 

I assume that this can be remedied by throwing the users table into the mix.

[xjermx]

This seems to do the trick in part:

 

 

SELECT mailmessage.text, users.name, mailmessage.timestamp, destination.touser, mailmessage.from_user

FROM (destination INNER JOIN mailmessage ON destination.whichmessage = mailmessage.ID_mailmessage)

INNER JOIN users

ON destination.touser = users.ID_users

 

It displays the text field, a name field (corresponding to the touser field), a timestamp, touser, and from_user.

 

It does not display the name corresponding with from_user, and I suspect that this is tricky because both TOUSER and FROM_USER use the same info, users.ID_users

 

Do I need to use a temporary table to fix this?

[fenway]

No, you need to alias your columns.

[xjermx]

Solved.

 

SELECT

  mailtext.messagetext,  FROMUSER.name AS sent_to, TOUSER.name as

sent_from, mailto.touser, mailtext.timestamp, mailtext.from_user

FROM

  mailtext, mailto, users FROMUSER, users TOUSER

WHERE

  mailto.whichmessage = mailtext.msg_ID and

  FROMUSER.users_id = mailtext.fromuser and

  TOUSER.users_id = mailto.touser