homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Using LEFT JOIN with multiple tables

Msg#: 4279137 posted 9:26 pm on Mar 9, 2011 (gmt 0)


I'm trying to retrieve data from 3 tables using a left join but there's some things I'm having problems understanding. The code below works.

$joins = "SELECT
porders.reqid, porders.podatesent, porders.porderno,
reqs.reqdatechanged, reqs.dateraised, reqs.reqno,
FROM porders
LEFT JOIN reqs ON porders.reqid = reqs.reqid
LEFT JOIN gdsin ON porders.reqid = gdsin.reqid WHERE `postatus` = 'processed' AND `poclientid` = '$client_id' ";

And the bold is my problem.

It didn't work when I had AND `clientid` = '$client_id' and the only commonality I could see was the column name. clientid is initiated on log in, stored in a session and used to retrieve all client content so its in all the tables.

As soon as I changed clientid to poclientid in the porders table it worked.

The reqid is also in all the tables and I use it to stitch the query together but when other columns (clientid) are the same it doesn't work, my fix is change the column name but that can't be right can it?

The other thing I don't understand is when I initiate my stipulations WHERE `postatus` = 'processed' AND `poclientid` = '$client_id' I'm referring to the porders table because postatus and poclientid are both in there but at the start of that line I LEFT JOIN gdsin. From this I'm assuming that mysql has access to them because I make a call from the porders table with the FROM porders part of the query.

That assumption sort of adds up to me but changing the column name in mysql surely can't be right can it?

I thought clientid was used by loads of applications for retrieving user defined content therefore it would be present in one table as the primary key and the other tables as the unique id. Do you have to change it to different column names if you want to use JOINS?

Any help would be much appreciated.



Msg#: 4279137 posted 1:29 pm on Mar 10, 2011 (gmt 0)

24 hours later I remember I could try the query in phpMyAdmin :-)

I changed the table back to clientid from poclient and tried it, got some more info, this error.

mysql #1052 - Column 'clientid;' in where clause is ambiguous.

Googled it and found many posts and a very concise answer:

Its just like the error is saying that your field 'catID' in where clause in ambiguous, this means that it doesnt know wich field you are using because the same field is in both tables (tbl_listings,tbl_category), so you need to tell to mysql witch one u want.

Changed my code to this and works well:

WHERE porders.postatus = 'processed' AND porders.clientid = '$clientid'

Hope it helps someone.


Msg#: 4279137 posted 4:11 am on Mar 29, 2011 (gmt 0)

WHERE `postatus` = 'processed' AND `poclientid` = '$client_id'

You are missing the alias.

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved