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,
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.