Results 1 to 4 of 4

Thread: SQL Query Help

  1. #1
    Join Date
    Mar 2011
    Posts
    3

    Unanswered: SQL Query Help

    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:

    Code:
    SELECT     *
    FROM         contacts
    WHERE     (id IN
                              (SELECT contact_id AS id
                                FROM          accounts_contacts
                                WHERE      (account_id IN
                                                           (SELECT     id_c AS account_id
                                                             FROM          accounts_cstm
                                                             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 so much for any help you can provide!

  2. #2
    Join Date
    Jun 2005
    Posts
    319
    Should be something like this:

    Code:
    SELECT
    *
    FROM
    CONTACTS O
    JOIN ACCOUNTS_CONTACTS R
    ON O.CONTACT_ID = R.CONTACT_ID
    WHERE EXISTS
    (
    SELECT 1 FROM ACCOUNTS_CSTM C
    WHERE R.ACCOUNT_ID = C.ACCOUNT_ID
    )

  3. #3
    Join Date
    Mar 2011
    Posts
    3
    Thanks for your response! I'll let you know how it goes.

  4. #4
    Join Date
    Mar 2011
    Posts
    3
    Thank you again, Gagnon Your query worked as expected. I modified it to get the customer number in there:

    Code:
    	SELECT
    	*
    	FROM
    	contacts O
    	JOIN accounts_contacts R
    	ON O.id = R.contact_id
    	WHERE EXISTS
    		(
    		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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •