Joins

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
REwingUK
Forum Commoner
Posts: 26
Joined: Wed Jul 29, 2009 8:46 pm

Joins

Post by REwingUK »

Hi Guys,

Hope you can help,

I'm trying to JOIN three tables, I've managed to do it for two tables but i'm struggling with the third table. This is my code so far

Code: Select all

mysql_query("SELECT * FROM users LEFT JOIN user_landlord_details ON users.ID = user_landlord_details.user_id WHERE users.ID = '".$message['from_user_id']."'")
The third table is called user_student_details and needs to join users.ID ON user_student_details.user_id

Any help would be great!
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Joins

Post by AbraCadaver »

There might be something better, but this may work:

Code: Select all

mysql_query("
SELECT * FROM users, user_landlord_details, user_student_details
WHERE (users.ID = user_landlord_details.user_id AND users.ID = user_student_details.user_id)
AND users.ID = '".$message['from_user_id']."'");
It depends on if you want to display records from a table only if the join id is found in the other table and that is complicated by having three tables.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Joins

Post by Weirdan »

Code: Select all

SELECT * 
FROM users 

LEFT JOIN user_landlord_details 
ON users.ID = user_landlord_details.user_id 

left join user_student_details
ON user_student_details.user_id=users.id

WHERE users.ID = 123
REwingUK
Forum Commoner
Posts: 26
Joined: Wed Jul 29, 2009 8:46 pm

Re: Joins

Post by REwingUK »

Perfect, Thank you both
REwingUK
Forum Commoner
Posts: 26
Joined: Wed Jul 29, 2009 8:46 pm

Re: Joins

Post by REwingUK »

Hi guys,

The join statement worked perfectly. Just one extra thing. I have duplicate names in the tables. How would i implement a AS statement into the SQL. So users.forename AS UserForename.

Thanks
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Joins

Post by Weirdan »

REwingUK wrote: How would i implement a AS statement into the SQL. So users.forename AS UserForename.
Exactly like you spelled it above: 'tablename.columname as alias'
REwingUK
Forum Commoner
Posts: 26
Joined: Wed Jul 29, 2009 8:46 pm

Re: Joins

Post by REwingUK »

does this go before the WHERE statement?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Joins

Post by Weirdan »

after 'SELECT' but before anything else.

http://dev.mysql.com/doc/refman/5.1/en/select.html
REwingUK
Forum Commoner
Posts: 26
Joined: Wed Jul 29, 2009 8:46 pm

Re: Joins

Post by REwingUK »

Works perfect, thanks a lot Weirdan
Post Reply