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
after 'SELECT' but before anything else.

http://dev.mysql.com/doc/refman/5.1/en/select.html

Re: Joins

Posted: Fri Feb 25, 2011 6:37 pm
by REwingUK
Works perfect, thanks a lot Weirdan