7 Replies Latest reply on Jan 19, 2017 4:47 PM by GZimme

    SQL Query Help

    JJGearhart Apprentice

      I am using to following query to find all contacts with email addresses. How would I alter this to also return contact 2 fields?

       

      Thanks for any help!

       

       

      select isnull(cse.contsupref,'')+isnull(cse.address1,'') as 'E-mail Address',

      contact1.contact,

      contact1.title,

      contact1.company,

      contact1.address1,

      contact1.address2,

      contact1.city,

      contact1.state,

      contact1.zip,

      contact1.phone1,

      contact1.key1,

      contact1.accountno

      from contact1

      inner join contsupp cse on (CONTACT1.ACCOUNTNO = cse.ACCOUNTNO AND cse.rectype = 'P' AND cse.u_contact = 'E-MAIL ADDRESS' AND cse.zip like '_1%')

      union

      select isnull(cse.contsupref,'')+isnull(cse.address1,'') as 'E-mail Address',

      contsupp.contact,

      contsupp.title,

      contact1.company,

      contsupp.address1,

      contsupp.address2,

      contsupp.city,

      contsupp.state,

      contsupp.zip,

      contsupp.phone,

      contact1.key1,

      contact1.accountno

      from contact1

      inner join contsupp on (CONTACT1.ACCOUNTNO = CONTSUPP.ACCOUNTNO AND contsupp.rectype = 'C' AND IsNull(contsupp.address1,'') = '')

      inner join contsupp cse on (contsupp.ACCOUNTNO = cse.ACCOUNTNO AND cse.linkacct = contsupp.recid AND cse.rectype = 'P' AND cse.u_contact = 'E-MAIL ADDRESS')

      union

      select isnull(cse.contsupref,'')+isnull(cse.address1,'') as 'E-mail Address',

      contsupp.contact,

      contsupp.title,

      contact1.company,

      contsupp.address1,

      contsupp.address2,

      contsupp.city,

      contsupp.state,

      contsupp.zip,

      contsupp.phone,

      contact1.key1,

      contact1.accountno

      from contact1

      inner join contsupp on (CONTACT1.ACCOUNTNO = CONTSUPP.ACCOUNTNO AND contsupp.rectype = 'C' AND IsNull(contsupp.address1,'') <> '')

      inner join contsupp cse on (contsupp.ACCOUNTNO = cse.ACCOUNTNO AND cse.linkacct = contsupp.recid AND cse.rectype = 'P' AND cse.u_contact = 'E-MAIL ADDRESS')

      order by company, contact

        • 1. Re: SQL Query Help
          John Neighbors Expert

          Jon,

           

          Looking closely,  you've got 3 distinct queries, joined by two UNION statements.

           

          Since each of the 3 distinct queries actually includes the CONTACT1 table, you could add the following to EACH of the 3 distinct queries and it should work well:

           

          LEFT JOIN CONTACT2 on (CONTACT1.accountno = CONTACT2.accountno)

           

          I'm simply sharing in this manner, rather than copy-n-pasting your entire query with my few additions.

           

          I'd suggest you put this on the next line AFTER the "from contact1" line in each case (for simplicity and consistency sake).  Once you've done this, you can THEN make reference to CONTACT2.<fieldnames> as you wish.  Just make sure you account for the CONTACT2 fields (or provide appropriate spacing/placeholders) in EACH of the 3 queries, as needed.

           

          I use a LEFT JOIN (vs. INNER JOIN) just in case any records do NOT have a corresponding CONTACT2 record, the other records will still be included.  If I used INNER JOIN instead, and any CONTACT1 records did NOT have the associated CONTACT2 record, then the CONTACT1 record (and associated email addresses, etc.) would be excluded from the query results altogether.  In my experience, this (excluding a record due simply to there NOT being a CONTACT2 record) is SELDOM what is desired.  So, using LEFT JOIN, as I have, is the safest approach.

           

          Hope this feedback helps. Please let me know if I need to clarify anything.

          • 2. Re: SQL Query Help
            Shaul.Bel Apprentice

            You can do it like this

             

             

            select * from CONTACT1

            inner join CONTACT2

            ON CONTACT1.ACCOUNTNO=CONTACT2.ACCOUNTNO

            left join

            (select isnull(CONTSUPREF,'')+isnull(address1,'') as 'E-mail Address',ACCOUNTNO from CONTSUPP where RECTYPE='P' and U_CONTACT='E-MAIL ADDRESS') CS

            ON CONTACT1.ACCOUNTNO=CS.ACCOUNTNO

             

            Replace the * with your needed fields from CONTACT1 / CONTACT2

            • 3. Re: SQL Query Help
              JJGearhart Apprentice

              Thank you both for your suggestions. I will try both approaches.

               

              Shaul Bel-- Will your much simplified query return both primary and additional contacts with email addresses?

              • 4. Re: SQL Query Help
                Shaul.Bel Apprentice

                In contsupp table you have 3 types of emails.

                Primary Emails

                Emails that are linked to an additional contact (when you double click on additional contact you can see the email that is linked to that contact)

                emails that are not linked to an additional contact.

                my query gets all the emails but you don't know if they are linked to specific additional contact.

                you can add to the sub query the field ADDRESS2 and you will get the name  if you will also add LINKACCT you will be able to know if it's linked to additional contact.

                The question is what's your goal.

                • 5. Re: SQL Query Help
                  JJGearhart Apprentice

                  Thank you for your follow-up information, Shaul Bel. My goal was to get a list of linked email addresses for primary and secondary contacts with other pertinent information from contact 1 and contact 2 table. John Neighbors' addition to my original query seems to give me exactly what I am looking for. I see the value of yours that it returns every email address whether linked or not. Your query gets me to our limit of 40000 records while John's and my query returns about 14500.

                  • 6. Re: SQL Query Help
                    GZimme Apprentice

                    This works great for what I want but I would like to only select     Where Contact.key2 like '%Customer%'

                     

                    Where would I add the where clause to give me contacts who have "Customer" in the Key2 field?

                     

                    I tried it after the   from contact1    but it was not happy?

                     

                    THANKS.

                    • 7. Re: SQL Query Help
                      GZimme Apprentice

                      I think I got it.   I put the Where clause after the Inner Joins and it seems to have worked!