NOT IN question - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

NOT IN question

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2010-02-09 : 05:15:21

I have three tables

Account
-------

code varchar(20),
clientid int,
accountname varchar(25)

code clientid accountname
---- -------- -----------
S001 2001 Smith & Sons
J101 1824 J Harpur Ltd

client
------

clientid int
currentbalance float

clientid currentbalance
-------- --------------
2001 34.26
1824 101.20

clientcategories
----------------
clientid int
clientcategorycode varchar(5)

clientid clientcategorycode
-------- ------------------

2001 0001
1862 0004
2001 0002
2001 0002
1824 0007

I want to return all the account codes where the
clientcategorycode is not '0001', '0002', '0003' or '0004'.

So, in the case of my example, it should return the code S001

How can I do this please?

I have tried:

SELECT a.code, a.accountname

FROM client as c
JOIN account as a
ON a.clientid = c.clientid
JOIN clientcategories as cc
ON cc.clientid = c.clientid

WHERE cc.clientcategorycode NOT IN ('0001', '0002', '0003', '0004')

without success.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 05:21:15

sorry your sample data contradicts your explanation. how do you get S001 for condition clientcategorycode is not '0001', '0002', '0003' or '0004'. S001 client id is 2001 which has category codes as 0001 & 0002

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2010-02-09 : 05:37:39

quote:
Originally posted by visakh16

how do you get S001 for condition clientcategorycode is not '0001'

You don't, because I am an idiot

I should have have typed that it should return J101

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 05:45:06

[code]SELECT a.*
FROM Account a
OUTER APPLY (SELECT COUNT(1) AS CatCnt
FROM clientcategories
WHERE clientid=a.clientid
AND clientcategorycode IN ('0001', '0002', '0003', '0004'))b
WHERE COALESCE(b.CatCnt,0) = 0
[/code]

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2010-02-09 : 06:27:23

Many thanks visakh16, that has nailed it.

Learned something worthwhile - hurrah!

Thanks again.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-09 : 06:57:58

quote:

Originally posted by visakh16

SELECT a.*
FROM Account a
OUTER APPLY (SELECT COUNT(1) AS CatCnt
             FROM clientcategories
             WHERE clientid=a.clientid
             AND clientcategorycode IN ('0001', '0002', '0003', '0004'))b
WHERE COALESCE(b.CatCnt,0) = 0

--I learn something when read your code! 
--You try to write/create non standard and odd query

SELECT a.*
FROM Account a
OUTER APPLY (SELECT COUNT(1) AS CatCnt
             FROM clientcategories
             WHERE c.clientid=a.clientid
             AND clientcategorycode IN ('0001', '0002', '0003', '0004'))b
WHERE COALESCE(b.CatCnt,0) = 0

--HERE ARE SOME EQUIVALENCE

SELECT *
  FROM Account a
 WHERE NOT EXISTS 
       (SELECT *
          FROM clientcategories c
         WHERE c.clientid=a.clientid
          AND clientcategorycode IN ('0001', '0002', '0003', '0004'));
          
   SELECT a.*
     FROM Account a
LEFT JOIN clientcategories c
       ON c.clientid=a.clientid
      AND clientcategorycode IN ('0001', '0002', '0003', '0004')
    WHERE c.clientid IS NULL
          

SELECT a.*
  FROM (SELECT clientid
          FROM Account          
        EXCEPT clientid        
          FROM clientcategories c
         WHERE clientcategorycode IN ('0001', '0002', '0003', '0004')) d
  JOIN Account a
    ON d.clientid=a.clientid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 07:35:25

quote:
Originally posted by ms65g
--I learn something when read your code!
--You try to write/create non standard and odd query

what according to you is 'standard query'?

also why do you think my suggestion is not standard?

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-09 : 07:40:33

I mean ANSI/ISO-SQL-1992/1989 syntax.
APPLY table operator is a nonstandard operator.
OUTER JOIN and EXISTS Predicare and EXCEPT set operator is Standard SQL.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 07:48:05

quote:
Originally posted by ms65g

I mean ANSI/ISO-SQL-1992/1989 syntax.
APPLY table operator is a nonstandard operator.
OUTER JOIN and EXISTS Predicare and EXCEPT set operator is Standard SQL.

My suggestion works in SQL 2005 which is what this forum is for. And we always do say that solutions given here are guaranteed to work only in MS SQL Server

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 03:54:29

quote:
Originally posted by OldMySQLUser

Many thanks visakh16, that has nailed it.

Learned something worthwhile - hurrah!

Thanks again.

You're welcome

Refer below to understand what all you can do with APPLY operator

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2010-02-10 : 04:08:51

Nice Blog! Many thanks for taking the time to create this and share your knowledge with the community.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 04:15:25

quote:
Originally posted by OldMySQLUser

Nice Blog! Many thanks for taking the time to create this and share your knowledge with the community.

Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources