+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: MySQL Query Two Tables Help (/showthread.php?tid=3372)
MySQL Query Two Tables Help - El Forum - 09-27-2007
[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
MySQL Query Two Tables Help - El Forum - 09-27-2007
[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'MySQL Query Two Tables Help - El Forum - 09-27-2007
[eluser]tribalab[/eluser]
Worked like a charm!
Thank you 座頭市!
MySQL Query Two Tables Help - El Forum - 01-08-2013
[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/inner-join-php.html