Hey all — I'd first like to thank you for reading my post. I'm extremely rusty in SQL (it's been nearly a decade since I've done anything significant), and need some guidance. There was a day when I'd have been able to solve this with no problem, but I'm incredibly stumped right now.
In summary, I'm exporting specific records from a couple of specific tables in an old CRM system and pulling them into a new one. The CRM system we're moving from is on SQL Server 2005.
Here are the tables I'm working with:
contacts - A table of contacts from which I'll be grabbing specific records.
accounts - A a table of accounts from which I'll be grabbing specific records. (it's there, but I haven't used it in the following statement)
accounts_cstm - Contains the custom fields my company added from SugarCRM's GUI.
accounts_contacts - linking the two.
As you likely assumed, accounts to contacts is one to many.
What I need:
I need to get all contacts for each account that contains data in a custom field.
What I've tried
I've tried nested statements to get the result I'm looking for:
WHERE (id IN
(SELECT contact_id AS id
WHERE (account_id IN
(SELECT id_c AS account_id
WHERE (LEN(customer_number_c) > 0)))))
The result was promising a couple weeks ago when I wrote this, but now that it's come to importing the latest data and comparing it to the search results for a given account on the front end, we're finding that we're missing people from that account. Not everyone's making it in.
I was trying to figure out how to do this using joins, but was unsure how to do that with the names of the fields varying...
I also thought that IS NOT NULL would work to test the customer number, but it was blowing lunch on me.
I've also tried running the subqueries on their own, and am going from 613 contact_ids to 590 rows in contacts at the top of the query... I thought that was bizarre, but, then again, you all probably think this whole bird nest is a senseless mess!
I think I've included all necessary information here, but I'll gladly add any additional info you may need to help me. I feel like an idiot posting this one, but I've so much on my plate and have spent so much time on this already that I just need to see if I can get some guidance from experienced SQL folks...
Thank you again, Gagnon — Your query worked as expected. I modified it to get the customer number in there:
JOIN accounts_contacts R
ON O.id = R.contact_id
SELECT 1 FROM accounts_cstm C
WHERE R.account_id = C.id_c AND (LEN(C.customer_number_c)>0)
And it appears to have worked. Does my modification look OK to you guys?
One more struggle I'm having:
I'm trying to modify this query to include the corresponding e-mails, which come from another table. This should be a relatively simple compound join, but I'm so rusty on this stuff it's embarrassing...
The email_addresses table, as you might imagine, contains e-mails for both accounts AND contacts, but I'm only interested in appending the appropriate e-mails to the list of contacts we're getting in the previous statement.
So there are two more tables I'm throwing into the mix here, and I'm unsure how to handle this.
email_addresses, containing the address, its unique id and some other info for each record that I'm OK with appending to the results.
email_addr_bean_rel, linking the e-mails to any record that can have them. It contains the aformentioned id contained in the e-mail table (email_address_id) and the 'bean_id,' which represents the record it belongs to, and can correspond to a contact, account, or pretty much any other record.
How can I modify that previous statement join with the email_address table on the relate table without breaking the current query?
One final question, and it's more a matter of understanding the fundamentals of SQL: In the rare cases where there are two e-mails for a given contact (per the rel table), what will the output be? Will it give me another record with a duplicate row containing the same contact record, but with a different set of e-mail columns?
Thank you all again for taking a look at my questions. I hate to do this, but I need to get this data into the new system as soon as possible, and I need the help of some professional DB folks.
If there's any way I can clarify, just let me know. I'll be working most of the night, and will respond as quickly as possible.