Page 1 of 1
Joins
Posted: Thu Feb 24, 2011 12:41 pm
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!
Re: Joins
Posted: Thu Feb 24, 2011 12:52 pm
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.
Re: Joins
Posted: Thu Feb 24, 2011 1:05 pm
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
Re: Joins
Posted: Thu Feb 24, 2011 1:28 pm
by REwingUK
Perfect, Thank you both
Re: Joins
Posted: Fri Feb 25, 2011 6:11 pm
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
Re: Joins
Posted: Fri Feb 25, 2011 6:23 pm
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'
Re: Joins
Posted: Fri Feb 25, 2011 6:29 pm
by REwingUK
does this go before the WHERE statement?
Re: Joins
Posted: Fri Feb 25, 2011 6:33 pm
by Weirdan
Re: Joins
Posted: Fri Feb 25, 2011 6:37 pm
by REwingUK
Works perfect, thanks a lot Weirdan