Whats wrong with this SQL Server Query?

[anicolais]

This query runs perfectly fine except all the emails come up as NULL. I have hundreds of emails related to contacts in my database but in this query the results say NULL for all emails. Any suggestions?

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.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 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.accounts_contacts.deleted = 0
ORDER BY sugarcrm_db.dbo.contacts.last_name

[jjwdesign]

This may be the problem.

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

OR/AND... I forget the exact order... You might want to add in some ( and ) in there...

[kuske]

I hope the blank char in line

LEFT JOIN sugarcrm_db.dbo.email_addresses ON sugarcrm_db.dbo.email_addr_bean_rel.email_address_ id = sugarcrm_db.dbo.email_addresses.id

in address_ id is a typo, isn't it?

So I got only null values for email too and checked the statement.

In the line

LEFT JOIN sugarcrm_db.dbo.email_addr_bean_rel ON sugarcrm_db.dbo.contacts.id = sugarcrm_db.dbo.email_addr_bean_rel.id

there must be a bean_id instead of id at the end

LEFT JOIN sugarcrm_db.dbo.email_addr_bean_rel ON sugarcrm_db.dbo.contacts.id = sugarcrm_db.dbo.email_addr_bean_rel.bean_id





Which module produces this query?

[anicolais]

I hope the blank char in line

LEFT JOIN sugarcrm_db.dbo.email_addresses ON sugarcrm_db.dbo.email_addr_bean_rel.email_address_ id = sugarcrm_db.dbo.email_addresses.id

in address_ id is a typo, isn't it?

So I got only null values for email too and checked the statement.

In the line

LEFT JOIN sugarcrm_db.dbo.email_addr_bean_rel ON sugarcrm_db.dbo.contacts.id = sugarcrm_db.dbo.email_addr_bean_rel.id

there must be a bean_id instead of id at the end

LEFT JOIN sugarcrm_db.dbo.email_addr_bean_rel ON sugarcrm_db.dbo.contacts.id = sugarcrm_db.dbo.email_addr_bean_rel.bean_id





Which module produces this query?

well this works better than before since it acutally gives me something now but it is not completely correct. I am trying to produce this query using contacts. I am running all contacts for a specific user. I am trying to relate the emails to these contacts. Now what this query gives me after I applied your changes is the same contact like 5 times each with the same email address. Any suggestions?

Also what did you think the address_ id is supposed to be as well?

Thanks!

[anicolais]

This is what the new query looks like

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 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.accounts.deleted = 0
AND sugarcrm_db.dbo.accounts_contacts.deleted = 0
AND sugarcrm_db.dbo.email_addr_bean_rel.deleted = 0
ORDER BY sugarcrm_db.dbo.contacts.last_name

[anicolais]

Also this does not return the contacts with no email addresses.

[kuske]

Replace
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'

by
WHERE sugarcrm_db.dbo.contacts.assigned_user_id = '367b274c-0723-3b01-2b96-4b2a7b024731'

and replace
AND sugarcrm_db.dbo.email_addr_bean_rel.deleted = 0

by
AND (sugarcrm_db.dbo.email_addr_bean_rel.deleted = 0
or sugarcrm_db.dbo.email_addr_bean_rel.bean_id IS NULL)

[anicolais]

This doesn't seem to work as it runs but nothing shows up. Since there is no way to show within each contact if there is an email address related to the contact is there a way to say if the contact does not have a bean.id that matches it's ID then to still run it? I was thinking this could be one solution or another could be to put a default address for an email account such as just a " ' " character just so it produces a bean ID but I dont know how I would do this either. Do you have any suggestions on either one of these? I appreciate all the help. Your suggestions have got me very close to getting it to work just not quite there yet.

Thanks

[anicolais]

The following seems to run the query right. I think the whole

AND sugarcrm_db.dbo.email_addresses.deleted = 0
AND sugarcrm_db.dbo.email_addr_bean_rel.deleted = 0

was what was screwing it up. I think this is what you were getting at in your posts. The final problem is if the account and contact and email are deleted from sugar the record still shows up in this query. Any suggestions?

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 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.accounts.deleted = 0
AND sugarcrm_db.dbo.accounts_contacts.deleted = 0
ORDER BY sugarcrm_db.dbo.contacts.last_name

[kuske]

If you did what I told you in my last post you got:

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.assigned_user_id = '367b274c-0723-3b01-2b96-4b2a7b024731'
AND sugarcrm_db.dbo.contacts.deleted = 0
AND sugarcrm_db.dbo.accounts.deleted = 0
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 )
ORDER BY sugarcrm_db.dbo.contacts.last_name

and that works.