not in method - 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

General SQL Server Forums

New to SQL Server Programming

not in method

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2014-05-08 : 22:27:33

i have a list of few columns..

columnB contains types of fruits..

i would like to pull a list of data which doesnt has apple..

columnA columnB columnC
1 Apple Alice
1 Pear John
1 Pear Margerat
2 Pear Daphine
2 Pear John
'
'
'
'

i would like not pull those doesnt has apple only, i tried not in method but it come out like this:

columnA columnB columnC
1 Pear John
1 Pear Margerat
2 Pear Daphine
2 Pear John
'
'
'
'

i would like to be like this:

columnA columnB columnC
2 Pear Daphine
2 Pear John
'
'
'
'

what method shall i use?

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-05-09 : 04:41:15

[code]SELECT *
FROM YourTable SOURCE
WHERE NOT EXISTS (
SELECT *
FROM YourTable FILTER
WHERE FILTER.columnB = 'Apple'
AND FILTER.columnA = SOURCE.columnA)[/code]

-------------------------------------
From Japan
Sorry, my English ability is limited.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-05-09 : 05:15:48

[code]
SELECT ColumnA,ColumnB,ColumnC
FROM
(
SELECT *,SUM(CASE WHEN ColumnB = 'Apple' THEN 1 ELSE 0 END) OVER (PARTITION BY ColumnA) AS Occ
FROM Table
)t
WHERE Occ=0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources