Help with MySQL Query

[anicolais]

Hi,
I am trying to run the following query. In this query it shows all contacts with an email address but does not show the ones without an email address. This is because of the second to last line "AND contacts.id = email_addr_bean_rel.bean_id". What I need is to run all the lines except this one. Then also run all the lines with this one and put the 2 pieces of data together so I have the contacts with and without email addresses. Is there a way to do this with some sort of case/if statement or what would be the best way to go about this? I am doing this right from a command line.

Thanks!

accounts_cstm.result_status_date_c,accounts_cstm.L istsource_c, accounts_cstm.product_interest_c,accounts.name,con tacts.first_name,contacts.last_name, contacts.title,contacts.phone_work, email_addresses.email_address, contacts.primary_address_street,contacts.primary_a ddress_city, contacts.primary_address_state, contacts.primary_address_postalcode,primary_addres s_country,notes.description
FROM notes, accounts, accounts_cstm, contacts, accounts_contacts,email_addr_bean_rel, email_addresses
WHERE accounts.id = accounts_cstm.id_c
AND accounts_contacts.contact_id = contacts.id
AND accounts_contacts.account_id = accounts.id
AND notes.contact_id = contacts.id
AND accounts_cstm.result_status_c LIKE 'Qualified Lead%'
AND contacts.id = email_addr_bean_rel.bean_id
GROUP BY contacts.last_name

[datasponge]

Hi,
I am trying to run the following query. In this query it shows all contacts with an email address but does not show the ones without an email address. This is because of the second to last line "AND contacts.id = email_addr_bean_rel.bean_id". What I need is to run all the lines except this one. Then also run all the lines with this one and put the 2 pieces of data together so I have the contacts with and without email addresses. Is there a way to do this with some sort of case/if statement or what would be the best way to go about this? I am doing this right from a command line.

Thanks!

accounts_cstm.result_status_date_c,accounts_cstm.L istsource_c, accounts_cstm.product_interest_c,accounts.name,con tacts.first_name,contacts.last_name, contacts.title,contacts.phone_work, email_addresses.email_address, contacts.primary_address_street,contacts.primary_a ddress_city, contacts.primary_address_state, contacts.primary_address_postalcode,primary_addres s_country,notes.description
FROM notes, accounts, accounts_cstm, contacts, accounts_contacts,email_addr_bean_rel, email_addresses
WHERE accounts.id = accounts_cstm.id_c
AND accounts_contacts.contact_id = contacts.id
AND accounts_contacts.account_id = accounts.id
AND notes.contact_id = contacts.id
AND accounts_cstm.result_status_c LIKE 'Qualified Lead%'
AND contacts.id = email_addr_bean_rel.bean_id
GROUP BY contacts.last_name

Try changing:

AND contacts.id = email_addr_bean_rel.bean_id

to

AND (contacts.id = email_addr_bean_rel.bean_id OR email_addr_bean_rel.bean_id IS NULL)

I'm not sure if you need to explicitly specify a LEFT JOIN to email_addr_bean_rel table. I'm more familiar with the explicit join syntax like:

SELECT contact.last_name, email_addresses.email_address FROM (contacts LEFT JOIN email_addr_bean_rel ON contacts.id = email_addr_bean_rel.contact_id) LEFT JOIN email_addresses ON email_addr_bean_rel.email_address_id = email_addresses.id WHERE (contacts.id = email_addr_bean_rel.bean_id OR email_addr_bean_rel.bean_id IS NULL);

So you could also convert the query to something similar using explicit LEFT JOINs.

Phil