Why does this query not give me contacts without emails?

[anicolais]

The following query shows all contacts with email addresses but does not show any contacts without email addresses. Why could this be? Thanks! SQL Server/Sugar 5.5CE

SELECT (sugarcrm_db.dbo.contacts.last_name +', '+ sugarcrm_db.dbo.contacts.first_name) AS "Contact Name", (CASE WHEN sugarcrm_db.dbo.accounts.name IS NULL THEN '' ELSE sugarcrm_db.dbo.accounts.name END) AS "Company", (CASE WHEN sugarcrm_db.dbo.contacts.phone_work IS NULL THEN '' ELSE sugarcrm_db.dbo.contacts.phone_work END) AS "Work Phone", (CASE WHEN sugarcrm_db.dbo.contacts.phone_mobile IS NULL THEN '' ELSE sugarcrm_db.dbo.contacts.phone_mobile END) AS "Mobile Phone", email_addresses.email_address AS "Email"
FROM sugarcrm_db.dbo.contacts LEFT JOIN sugarcrm_db.dbo.accounts_contacts ON sugarcrm_db.dbo.contacts.id = sugarcrm_db.dbo.accounts_contacts.contact_id
LEFT JOIN sugarcrm_db.dbo.accounts ON sugarcrm_db.dbo.accounts.id = sugarcrm_db.dbo.accounts_contacts.account_id
LEFT JOIN sugarcrm_db.dbo.email_addr_bean_rel ON sugarcrm_db.dbo.contacts.id = sugarcrm_db.dbo.email_addr_bean_rel.bean_id
LEFT JOIN sugarcrm_db.dbo.email_addresses ON sugarcrm_db.dbo.email_addr_bean_rel.email_address_ id = sugarcrm_db.dbo.email_addresses.id
WHERE sugarcrm_db.dbo.contacts.id = sugarcrm_db.dbo.email_addr_bean_rel.bean_id OR sugarcrm_db.dbo.email_addr_bean_rel.bean_id = ''
AND sugarcrm_db.dbo.contacts.assigned_user_id = '367b274c-0723-3b01-2b96-4b2a7b024731'
AND sugarcrm_db.dbo.contacts.deleted = 0
AND sugarcrm_db.dbo.email_addresses.deleted = 0
AND sugarcrm_db.dbo.accounts_contacts.deleted = 0
AND sugarcrm_db.dbo.email_addr_bean_rel.deleted = 0
AND sugarcrm_db.dbo.accounts.deleted = 0
ORDER BY sugarcrm_db.dbo.contacts.last_name

[datasponge]

Anicois,

From a quick look I see two problems:
1. WHERE Precedence for the OR vs AND
2. Checking '' and 0 vs NULL

Your WHERE clause is:

WHERE sugarcrm_db.dbo.contacts.id = sugarcrm_db.dbo.email_addr_bean_rel.bean_id OR sugarcrm_db.dbo.email_addr_bean_rel.bean_id = ''
AND sugarcrm_db.dbo.contacts.assigned_user_id = '367b274c-0723-3b01-2b96-4b2a7b024731'
AND sugarcrm_db.dbo.contacts.deleted = 0
AND sugarcrm_db.dbo.email_addresses.deleted = 0
AND sugarcrm_db.dbo.accounts_contacts.deleted = 0
AND sugarcrm_db.dbo.email_addr_bean_rel.deleted = 0
AND sugarcrm_db.dbo.accounts.deleted = 0

When there is an unmatched table in a LEFT JOIN, all fields in that table are NULL. So your checks against '' and 0 will not match any records and so will exclude all records as in the lines:

OR sugarcrm_db.dbo.email_addr_bean_rel.bean_id = ''

AND sugarcrm_db.dbo.email_addresses.deleted = 0

and

AND sugarcrm_db.dbo.email_addr_bean_rel.deleted = 0

You still get the matching records because your precedence in the first line is OR'ed before any of the AND's defeating the purpose of checking any of the deleted values.

You need to use parenthesis and consider what you want to include to make it work they way you want like for example:

WHERE (sugarcrm_db.dbo.contacts.id = sugarcrm_db.dbo.email_addr_bean_rel.bean_id OR sugarcrm_db.dbo.email_addr_bean_rel.bean_id IS NULL)
AND sugarcrm_db.dbo.contacts.assigned_user_id = '367b274c-0723-3b01-2b96-4b2a7b024731'
AND sugarcrm_db.dbo.contacts.deleted = 0
AND (sugarcrm_db.dbo.email_addresses.deleted = 0 OR sugarcrm_db.dbo.email_addr_bean_rel.bean_id IS NULL)
AND sugarcrm_db.dbo.accounts_contacts.deleted = 0
AND (sugarcrm_db.dbo.email_addr_bean_rel.deleted = 0 OR sugarcrm_db.dbo.email_addr_bean_rel.bean_id IS NULL)
AND sugarcrm_db.dbo.accounts.deleted = 0

You might also want to add a similar OR statement for accounts if unmatched accounts are ok.

Phil