[Resolved] returning no values from JOIN MySQL query

[perpetualshaun]

I wrote a MySQL query w/ a JOIN in it that works like a champ if there are values in the joined table. But what about when there are no values? Heres' my query:

SELECT account_id, account_name, account_billto_address, account_shipto_address, account_status, account_created_on, account_created_by_fname, account_created_by_lname, account_created_by_email, account_revised_on, account_revised_by_fname, account_revised_by_lname, account_revised_by_email, account_websites.website_value AS website, account_pocs.account_poc_type AS primary_contact_type, account_pocs.account_poc_value AS primary_contact
FROM accounts
LEFT JOIN account_websites ON account_id = website_account_id
LEFT JOIN account_pocs ON account_id = account_poc_account_id
WHERE 1 AND account_websites.website_pri =1 AND account_pocs.account_poc_pri =1
GROUP BY account_id
ORDER BY account_name ASC 
LIMIT 0 , 50 

I only have 1 record in my accounts table at the moment, but when I emptied the account_pocs table, it comes back w/ 0 rows. How can I revise this query ty still pull back the matches in the accounts table even if there are no records in the account_websites and/or the account_pocs tables?

Thanks,

Shaun

[tomhath]

The WHERE clause acts on the result of the join. So refer to columns in LEFT JOINed tables in the ON clause of the JOIN:

SELECT account_id, account_name, account_billto_address, 
       account_shipto_address, account_status, account_created_on, 
       account_created_by_fname, account_created_by_lname, 
       account_created_by_email, account_revised_on, 
       account_revised_by_fname, account_revised_by_lname, 
       account_revised_by_email, account_websites.website_value AS website, 
       account_pocs.account_poc_type AS primary_contact_type, 
       account_pocs.account_poc_value AS primary_contact
FROM accounts
LEFT JOIN account_websites 
ON    account_id = website_account_id
  AND account_websites.website_pri =1 
LEFT JOIN account_pocs 
ON    account_id = account_poc_account_id
  AND account_pocs.account_poc_pri =1
WHERE 1 
GROUP BY account_id
ORDER BY account_name ASC 
LIMIT 0 , 50