1 Reply Latest reply on May 17, 2017 3:36 PM by John Neighbors

    Sql Query not working as expected

    dariobavicchi1 Apprentice

      I have the below query that we made to filter:

      all contacts from 'Zone1' and 'Zone2' of category 'Toys' and 'Food'

      that have a primary email that is not marked in mergecodes as ERR or OLD


      The query is working but we found that it reports only contacts that have more than 1 detail row

      So contacts with only 1 email address and no addictional contacts are not in the results

      If i kill the cs.mergecodes query section, it works well but the query will contains emails with ERR or OLD mergecodes (that we can't use in our mailing campaign)


      I can't understand why it's not working...

      ...any help apprerciated




      select c1.*, cs.contsupref, cs.mergecodes

      FROM     contact1 c1

               INNER JOIN contsupp cs

               ON       c1.accountno      = cs.accountno

      where (C1.Key1 = 'zone1' or C1.Key1 = 'zone2') and (C1.Key2 = 'Toys' OR C1.Key2 = 'Food') and cs.contact='E-Mail Address' and substring(cs.zip,2,1)='1' and cs.mergecodes <> 'ERR' and cs.mergecodes <> 'OLD' ORDER BY C1.CONTACT

        • 1. Re: Sql Query not working as expected
          John Neighbors SSMMVPGroup

          I suspect the "problem" stems from the JOIN being defined so "loosely" (for lack of a better description).


          Without having your data to run this against, see if this helps:



             isnull(cs.CONTSUPREF,'')+isnull(cs.ADDRESS1,'') AS EmailAddress

          , isnull(cs.MERGECODES,'') as 'EmailMergeCodes'

          , c1.*

          FROM contact1 c1

          INNER JOIN contsupp cs ON

            (c1.ACCOUNTNO = cs.ACCOUNTNO

            AND cs.RECTYPE = 'P'

            AND cs.CONTACT ='E-Mail Address'

            AND substring(isnull(cs.ZIP,''),2,1)='1'


          where isnull(C1.Key1,'') in ('zone1','zone2')

            AND isnull(C1.Key2,'')  in ('Toys','Food')

            AND isnull(cs.MERGECODES,'') not like '%ERR%'

            AND isnull(cs.mergecodes,'') not like '%OLD%'

            ORDER BY C1.CONTACT


          I made a number of subjective, immaterial changes as well, just striving for better readability.


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