| Author |
 Topic  |
|
|
OldMySQLUser
Constraint Violating Yak Guru
301 Posts |
 Posted - 02/09/2010 : 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
India
47069 Posts |
Posted - 02/09/2010 : 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 - 02/09/2010 : 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
India
47069 Posts |
Posted - 02/09/2010 : 05:45:06
|
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
|
|
|
|
OldMySQLUser
Constraint Violating Yak Guru
301 Posts |
Posted - 02/09/2010 : 06:27:23
|
Many thanks visakh16, that has nailed it.
Learned something worthwhile - hurrah! 
Thanks again. |
|
|
|
ms65g
Constraint Violating Yak Guru
497 Posts |
Posted - 02/09/2010 : 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
|
Edited by - ms65g on 02/09/2010 07:00:24 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 02/09/2010 : 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 - 02/09/2010 : 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. |
Edited by - ms65g on 02/09/2010 07:42:55 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 02/09/2010 : 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
India
47069 Posts |
Posted - 02/10/2010 : 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 - 02/10/2010 : 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
India
47069 Posts |
Posted - 02/10/2010 : 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 |
|
|
| |
Topic |
|
NOT IN question
