SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 NOT IN question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 02/09/2010 :  05:15:21  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/09/2010 :  05:21:15  Show Profile  Reply with Quote
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
Go to Top of Page

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 02/09/2010 :  05:37:39  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/09/2010 :  05:45:06  Show Profile  Reply with Quote
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
Go to Top of Page

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 02/09/2010 :  06:27:23  Show Profile  Reply with Quote
Many thanks visakh16, that has nailed it.

Learned something worthwhile - hurrah!

Thanks again.
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 02/09/2010 :  06:57:58  Show Profile  Reply with Quote
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

Edited by - ms65g on 02/09/2010 07:00:24
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/09/2010 :  07:35:25  Show Profile  Reply with Quote
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?
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 02/09/2010 :  07:40:33  Show Profile  Reply with Quote
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.

Edited by - ms65g on 02/09/2010 07:42:55
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/09/2010 :  07:48:05  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/10/2010 :  03:54:29  Show Profile  Reply with Quote
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
Go to Top of Page

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 02/10/2010 :  04:08:51  Show Profile  Reply with Quote
Nice Blog! Many thanks for taking the time to create this and share your knowledge with the community.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/10/2010 :  04:15:25  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000