How wrong... join tables and order by problem revisited

[The_Chancer]

OK the initial code worked:

$query="SELECT * FROM enquiry WHERE orighotelid='".$_COOKIE['ccslocation']."' AND taken_by!='' AND enquiry_type='$search' order by ref desc";

However, I pulled the wrong ref out (I pulled the one from agents table) so it changed to:

$query = "SELECT E.ref as enqref,
date_taken as date_taken,
company_agent as company_agent,
agtco_name as agtco_name,
client_name as client_name,
enquiry_type as enquiry_type,
func_date AS func_date,
phone_no as phone_no,
taken_by as taken_by,
hotel_var as hotel_var
FROM enquiry AS E INNER JOIN agents AS A on A.ref=E.agtco_name
WHERE orighotelid='".$_COOKIE['ccslocation']."'
AND func_date!='' AND enquiry_type in ('Enquiry','Provisional')
ORDER BY A.company,E.ref $type";

Little long winded - but it works...

Now I have another slight problem - if the agtco_name in the initial enquiry is blank - it is skipped totally. I would like to keep this in the listing - but how ?

I am stumped - any ideas ?

[LordShryku]

Sounds like you just need a left join. I'd like to also add that the renaming of fields to the ame name isn't needed. Try this...

$query = "SELECT E.ref as enqref,
                 date_taken,
                 company_agent,
                 agtco_name,
                 client_name,
                 enquiry_type,
                 func_date,
                 phone_no,
                 taken_by,
                 hotel_var,
            FROM enquiry AS E 
       LEFT JOIN agents AS A 
              ON A.ref=E.agtco_name
           WHERE orighotelid='".$_COOKIE['ccslocation']."'
             AND func_date!='' 
             AND enquiry_type in ('Enquiry','Provisional')
        ORDER BY A.company,E.ref $type";