Syntax for this join

[Fuffle]

I think this is quite a common operation so hopefully the answer is simple

 

So (for example) we have these tables:

users (
`user_id` int(10),
`username` varchar(20)
)

private_messages (
`message_id` int(10),
`from` int(10),
`to` int(10),
`subject` varchar(255),
`message` text
)

 

and the desired result is to echo out a list of messages where to/from is the current user_id for the session, along with the username of the sender/recipient (from/to) and the subject for each message, so we presumably need to do a join with something like

 

$sql = "SELECT pms.message_id , pms.from, pms.to, pms.subject, u.username
FROM private_messages pms, users u WHERE pms.from = $session->user_id OR pms.to = $session->user_id";

 

as the basic query, but im not sure how to build on this to make it do what I want. How do I select the username from the users table twice in the same query using different user_ids each time? Should I be doing something like this:

 

$sql = "SELECT pms.message_id , pms.from, pms.to, pms.subject, u1.username as from_name, u2.username as to_name
FROM private_messages pms, users u1, users u2 WHERE pms.from = $session->user_id OR pms.to = $session->user_id AND u1.username = pms.from AND u2.username = pms.to";

 

? Obviously that won't work but am even on the right track?

 

Help appreciated

[fenway]

Try this (untested);

 

$sql = "SELECT pms.message_id , pms.from, pms.to, pms.subject, u1.username as from_name, u2.username as to_name
FROM private_messages pms
INNER JOIN users u1 ON ( u1.username = pms.from )
INNER JOIN users u2 ON ( u2.username = pms.to )
WHERE ( pms.from = $session->user_id OR pms.to = $session->user_id ) ";