MySQL Query Two Tables Help

[eluser]tribalab[/eluser]
I'm trying to find the most effecient way to list messages sent to a user, but cross reference two tables to resolve userids that are used in the mail table with the names stores in the users table.

here are two simple tables:

USERS: [ ID, FIRSTNAME, LASTNAME ]
MAIL: [ TO, FROM, STATUS, SUBJECT, MESSAGE, DATE ];

The mail.to and mail.from are the respective users.id values. I'd like to do a single query to get all entries in mail where status='unread', but in addition to getting the id's for each message, I would like to get the users' firstname+" "+lastname as well.

So something like a combo of:
"SELECT * FROM mail WHERE mail.to=1" & foreach... "SELECT firstname, lastname FROM users WHERE id=".$mailfrom

Thanks for the help!

Aaron

[eluser]座頭市[/eluser]
I'd do it with JOINs rather than multiple queries.

Code:

SELECT x.*, y.firstname AS to_firstname, y.lastname AS to_lastname, z.firstname AS from_firstname, z.lastname AS from_lastname
FROM mail AS x
JOIN users AS y ON x.to = y.id
JOIN users AS z ON x.from = z.id
WHERE x.to = ?
AND x.status = 'unread'

[eluser]tribalab[/eluser]
Worked like a charm!

Thank you 座頭市!

[eluser]pushpa[/eluser]
Why don't you use MYSQL join statement
$sql ="SELECT product.product_name, customer.customer_name FROM product
INNER JOIN customer ON customer.product_id=product.product_id ";
$result=mysql_query($sql);
Source:-http://wintekweb.blogspot.com/2012/06/in...n-php.html