Combination of Columns Not in Another Table

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

General SQL Server Forums

New to SQL Server Programming

Combination of Columns Not in Another Table

Author

Topic

BobRoberts
Posting Yak Master

109 Posts

Posted - 2014-05-13 : 20:07:36

I want a list of rows in one table where a combination of two columns aren't in another table. One column is character and the second column is date/time. This happens to be SQL 2005. My first attempt using only one column worked OK:

SELECT * FROM TABLEA
WHERE CUSTID NOT IN (SELECT CUSTNO FROM TABLEB)

But when I added the combination of customer number and sales date, it made the query "run away" - I had to cancel it and it didn't return anything:

SELECT * FROM TABLEA
WHERE CUSTID + CONVERT(VARCHAR, SLS_DATE, 101) NOT IN
(SELECT CUSTNO + CONVERT(VARCHAR, SALES_DATE, 101) FROM TABLEB)

I wonder what might be wrong, but also whether this is the correct approach to begin with. Thanks for your attention.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-13 : 20:09:28

SELECT * FROM TABLEA a
WHERE NOT EXISTS (SELECT * FROM TABLEB b WHERE a.CUSTNO = b.CUSTNO AND a.SALES_DATE = b.SALES_DATE)

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

BobRoberts
Posting Yak Master

109 Posts

Posted - 2014-05-14 : 13:06:55

It works splendidly - thanks!

quote:
Originally posted by tkizer

SELECT * FROM TABLEA a
WHERE NOT EXISTS (SELECT * FROM TABLEB b WHERE a.CUSTNO = b.CUSTNO AND a.SALES_DATE = b.SALES_DATE)

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-14 : 13:49:38

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources