Need help with this crazy select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help with this crazy select statement

Author

Topic

twestfall
Starting Member

3 Posts

Posted - 2014-09-26 : 13:12:00

This should be a fairly easy select statement but is giving me fits.

I am running a select statement from a SalesLogix database and the results make no sense at all.

The following statement results in 27,575 rows.

SELECT A1.CONTACTID, A1.EMAIL, A1.ACCOUNT, A1.FIRSTNAME, A1.LASTNAME, A2.STATE A2_STATE, A1.SECCODEID,
A3.INCLUDE_ON_MAILINGS A3_INCLUDE_ON_MAILINGS, A5.INCLUDE_IN_MAILINGS A5_INCLUDE_IN_MAILINGS,
A3.BULK_LASTMAILING A3_BULK_LASTMAILING, A3.BULK_DELIVERY_FAILED A3_BULK_DELIVERY_FAILED,
A3.BULK_UNSUBSCRIBED A3_BULK_UNSUBSCRIBED, A3.BULK_COMMENT A3_BULK_COMMENT,
A3.AEV_VERIFICATIONRESULT A3_AEV_VERIFICATIONRESULT, A2.POSTALCODE A2_POSTALCODE, A2.COUNTRY A2_COUNTRY,
A4.SECCODEID A4_SECCODEID
FROM CONTACT A1
LEFT OUTER JOIN ACCOUNT A4 ON (A1.ACCOUNTID=A4.ACCOUNTID)
LEFT OUTER JOIN INF_ACCOUNT_EXT A5 ON (A4.ACCOUNTID=A5.ACCOUNTID)
LEFT OUTER JOIN ADDRESS A2 ON (A1.ADDRESSID=A2.ADDRESSID)
LEFT OUTER JOIN INF_CONTACT_EXT A3 ON (A1.CONTACTID=A3.CONTACTID)
WHERE (UPPER(A5.INCLUDE_IN_MAILINGS)='T')
AND (UPPER(A3.INCLUDE_ON_MAILINGS)='T')
AND (UPPER(A3.AEV_VERIFICATIONRESULT)<>'BAD')
AND (A1.EMAIL IS NOT NULL AND A1.EMAIL <> '')
AND (A3.BULK_UNSUBSCRIBED IS NULL)
ORDER BY A4.SECCODEID ASC

Now - I want to exclude all those who are in the country of Canada so I change the select statement to this. This statement results in 10,924 rows. I know that is not accurate. Hmmm...

SELECT A1.CONTACTID, A1.EMAIL, A1.ACCOUNT, A1.FIRSTNAME, A1.LASTNAME, A2.STATE A2_STATE, A1.SECCODEID,
A3.INCLUDE_ON_MAILINGS A3_INCLUDE_ON_MAILINGS, A5.INCLUDE_IN_MAILINGS A5_INCLUDE_IN_MAILINGS,
A3.BULK_LASTMAILING A3_BULK_LASTMAILING, A3.BULK_DELIVERY_FAILED A3_BULK_DELIVERY_FAILED,
A3.BULK_UNSUBSCRIBED A3_BULK_UNSUBSCRIBED, A3.BULK_COMMENT A3_BULK_COMMENT,
A3.AEV_VERIFICATIONRESULT A3_AEV_VERIFICATIONRESULT, A2.POSTALCODE A2_POSTALCODE, A2.COUNTRY A2_COUNTRY,
A4.SECCODEID A4_SECCODEID
FROM CONTACT A1
LEFT OUTER JOIN ACCOUNT A4 ON (A1.ACCOUNTID=A4.ACCOUNTID)
LEFT OUTER JOIN INF_ACCOUNT_EXT A5 ON (A4.ACCOUNTID=A5.ACCOUNTID)
LEFT OUTER JOIN ADDRESS A2 ON (A1.ADDRESSID=A2.ADDRESSID)
LEFT OUTER JOIN INF_CONTACT_EXT A3 ON (A1.CONTACTID=A3.CONTACTID)
WHERE (UPPER(A5.INCLUDE_IN_MAILINGS)='T')
AND (UPPER(A3.INCLUDE_ON_MAILINGS)='T')
AND (UPPER(A3.AEV_VERIFICATIONRESULT)<>'BAD')
AND (A1.EMAIL IS NOT NULL AND A1.EMAIL <> '')
AND (A3.BULK_UNSUBSCRIBED IS NULL)
AND (A2.COUNTRY <> 'CANADA')
ORDER BY A4.SECCODEID ASC

So now I just change the "AND (A2.COUNTRY <> 'CANADA')" to "AND (A2.COUNTRY = 'CANADA')" and that results in 1,973 rows. Huh??

SELECT A1.CONTACTID, A1.EMAIL, A1.ACCOUNT, A1.FIRSTNAME, A1.LASTNAME, A2.STATE A2_STATE, A1.SECCODEID,
A3.INCLUDE_ON_MAILINGS A3_INCLUDE_ON_MAILINGS, A5.INCLUDE_IN_MAILINGS A5_INCLUDE_IN_MAILINGS,
A3.BULK_LASTMAILING A3_BULK_LASTMAILING, A3.BULK_DELIVERY_FAILED A3_BULK_DELIVERY_FAILED,
A3.BULK_UNSUBSCRIBED A3_BULK_UNSUBSCRIBED, A3.BULK_COMMENT A3_BULK_COMMENT,
A3.AEV_VERIFICATIONRESULT A3_AEV_VERIFICATIONRESULT, A2.POSTALCODE A2_POSTALCODE, A2.COUNTRY A2_COUNTRY,
A4.SECCODEID A4_SECCODEID
FROM CONTACT A1
LEFT OUTER JOIN ACCOUNT A4 ON (A1.ACCOUNTID=A4.ACCOUNTID)
LEFT OUTER JOIN INF_ACCOUNT_EXT A5 ON (A4.ACCOUNTID=A5.ACCOUNTID)
LEFT OUTER JOIN ADDRESS A2 ON (A1.ADDRESSID=A2.ADDRESSID)
LEFT OUTER JOIN INF_CONTACT_EXT A3 ON (A1.CONTACTID=A3.CONTACTID)
WHERE (UPPER(A5.INCLUDE_IN_MAILINGS)='T')
AND (UPPER(A3.INCLUDE_ON_MAILINGS)='T')
AND (UPPER(A3.AEV_VERIFICATIONRESULT)<>'BAD')
AND (A1.EMAIL IS NOT NULL AND A1.EMAIL <> '')
AND (A3.BULK_UNSUBSCRIBED IS NULL)
AND (A2.COUNTRY = 'CANADA')
ORDER BY A4.SECCODEID ASC

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-26 : 13:46:21

what's the question? In the last query you're limiting the results to those where the country is canada. In the previous query you're doing exactly the opposite.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-26 : 13:47:58

It's because you are using a LEFT JOIN for the table you are doing the exclusion. Check this article for details: http://www.sqlteam.com/article/additional-criteria-in-the-join-clause

In short, move the exclusion criteria to the LEFT JOIN ON condition.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

twestfall
Starting Member

3 Posts

Posted - 2014-09-29 : 10:12:13

gbritton - My question is that why when I ask specifically "AND (A2.COUNTRY = 'CANADA')" that I get exactly what I was hoping for, the 1,973 records that have an address where the country ='CANADA'. With that logic, one would think that by doing the opposite and asking "AND (A2.COUNTRY <> 'CANADA')" that I would get 25,602 records (27,575 - 1,973) but I don't, I get 10,924 and I have no idea why.

tkizer - based on your suggestion I change the query to:

SELECT A1.CONTACTID, A1.EMAIL, A1.ACCOUNT, A1.FIRSTNAME, A1.LASTNAME, A2.STATE A2_STATE, A1.SECCODEID,
A3.INCLUDE_ON_MAILINGS A3_INCLUDE_ON_MAILINGS, A5.INCLUDE_IN_MAILINGS A5_INCLUDE_IN_MAILINGS,
A3.BULK_LASTMAILING A3_BULK_LASTMAILING, A3.BULK_DELIVERY_FAILED A3_BULK_DELIVERY_FAILED,
A3.BULK_UNSUBSCRIBED A3_BULK_UNSUBSCRIBED, A3.BULK_COMMENT A3_BULK_COMMENT,
A3.AEV_VERIFICATIONRESULT A3_AEV_VERIFICATIONRESULT, A2.POSTALCODE A2_POSTALCODE, A2.COUNTRY A2_COUNTRY,
A4.SECCODEID A4_SECCODEID
FROM CONTACT A1
LEFT OUTER JOIN ACCOUNT A4 ON (A1.ACCOUNTID=A4.ACCOUNTID)
LEFT OUTER JOIN INF_ACCOUNT_EXT A5 ON (A4.ACCOUNTID=A5.ACCOUNTID)
LEFT OUTER JOIN ADDRESS A2 ON (A1.ADDRESSID=A1.ADDRESSID AND A2.COUNTRY <> 'CANADA')
LEFT OUTER JOIN INF_CONTACT_EXT A3 ON (A1.CONTACTID=A3.CONTACTID)
WHERE (UPPER(A5.INCLUDE_IN_MAILINGS)='T')
AND (UPPER(A3.INCLUDE_ON_MAILINGS)='T')
AND (UPPER(A3.AEV_VERIFICATIONRESULT)<>'BAD')
AND (A1.EMAIL IS NOT NULL AND A1.EMAIL <> '')
AND (A3.BULK_UNSUBSCRIBED IS NULL)
ORDER BY A4.SECCODEID ASC

This was really bad. I stopped the query after over 5 minutes and it had returned 1,220,4727 records up to that point. So, I'm back scratching my head.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-29 : 11:23:51

Mistake here:


LEFT OUTER JOIN ADDRESS A2 ON (A1.ADDRESSID=A1.ADDRESSID AND A2.COUNTRY <> 'CANADA')

should be:


LEFT OUTER JOIN ADDRESS A2 ON (A1.ADDRESSID=A2.ADDRESSID AND A2.COUNTRY <> 'CANADA')

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-29 : 12:58:14

quote:
Originally posted by twestfall

This was really bad. I stopped the query after over 5 minutes and it had returned 1,220,4727 records up to that point. So, I'm back scratching my head.

You typo'd the join condition (A1.ADDRESSID=A1.ADDRESSID). See gbritton's correction where one uses A1 and the other uses A2.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

twestfall
Starting Member

3 Posts

Posted - 2014-10-07 : 15:44:51

So I fixed my typo (oops) to look like this:

SELECT A1.CONTACTID, A1.EMAIL, A1.ACCOUNT, A1.FIRSTNAME, A1.LASTNAME, A2.STATE A2_STATE, A1.SECCODEID,
A3.INCLUDE_ON_MAILINGS A3_INCLUDE_ON_MAILINGS, A5.INCLUDE_IN_MAILINGS A5_INCLUDE_IN_MAILINGS,
A3.BULK_LASTMAILING A3_BULK_LASTMAILING, A3.BULK_DELIVERY_FAILED A3_BULK_DELIVERY_FAILED,
A3.BULK_UNSUBSCRIBED A3_BULK_UNSUBSCRIBED, A3.BULK_COMMENT A3_BULK_COMMENT,
A3.AEV_VERIFICATIONRESULT A3_AEV_VERIFICATIONRESULT, A2.POSTALCODE A2_POSTALCODE, A2.COUNTRY A2_COUNTRY,
A4.SECCODEID A4_SECCODEID
FROM CONTACT A1
LEFT OUTER JOIN ACCOUNT A4 ON (A1.ACCOUNTID=A4.ACCOUNTID)
LEFT OUTER JOIN INF_ACCOUNT_EXT A5 ON (A4.ACCOUNTID=A5.ACCOUNTID)
LEFT OUTER JOIN ADDRESS A2 ON (A1.ADDRESSID=A2.ADDRESSID AND A2.COUNTRY <> 'CANADA')
LEFT OUTER JOIN INF_CONTACT_EXT A3 ON (A1.CONTACTID=A3.CONTACTID)
WHERE (UPPER(A5.INCLUDE_IN_MAILINGS)='T')
AND (UPPER(A3.INCLUDE_ON_MAILINGS)='T')
AND (UPPER(A3.AEV_VERIFICATIONRESULT)<>'BAD')
AND (A1.EMAIL IS NOT NULL AND A1.EMAIL <> '')
AND (A3.BULK_UNSUBSCRIBED IS NULL)
ORDER BY A4.SECCODEID ASC

And in doing so, received the same results whether I used AND A2.COUNTRY <> 'CANADA' or AND A2.COUNTRY = 'CANADA'. And actually that makes some sense to me since with a left join i'm saying I want to keep all the records on the left whether they exist or not on the right. If I change it to a right join then I get the same results as I was getting before when I had the condition in the where clause.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-10-07 : 18:34:13

Is it possible that some of the "COUNTRY" values are NULL; either because the Address table holds a NULL Value or the outer join condition results in no data (and hence NULL)? In these cases the value would be neither 'CANADA' nor not 'CANADA'. See if getting a COUNT where the Country is NULL yields the missing entries.

No amount of belief makes something a fact. -James Randi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources