[SOLVED] SELECT statement problem...

[CRCameron]

hey, need a little help with a slightly more complex select statement....

 

i have a table that stores messages sent from one user to another....so in the table 2 of my fields are  senderid, reciverid,

 

the senderid and reciverid are to link to the user table where the sender of reciver id will match the userid in the user table

 

my problem is that i cant display the username (stored in users table) of both these users cause if i used a 'WHERE user.userid=mail.senderid' then i would obviously only get the username of the sender...

 

anybody got any suggestions?

[d22552000]

to do sql on a seperate table from the one currently in:

 

''.''

 

In your instance you would need to call the usernames like this:

$result1 = "WHERE user.userid=mail.senderid'";
$result2 = "WHERE '".$result1."'.'userid' == 'user.userid'";

 

That example is in php though...

[CRCameron]

thanks for the help but i didnt really understand that...

 

could i possible get example code with an explantation of the code...or possibly just a link to somewhere that shows me how to do it properly...

[kirk112]

Is this what you where after?

 

SELECT u1.username AS sender, u2.username AS reciver

FROM mail AS m

LEFT JOIN users AS u1 ON m.senderid = u1.user_id

LEFT JOIN users AS u2 ON m.reciver = u2.user_id

[CRCameron]

Yes that is exactly what i was looking for...thank you very much