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

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-07-11 : 04:53:34

i have ID number and status.

how can i not include those status which has closed?

i tried to use not in but did not work.

ID status
1 new
1 old
1 closed
2 new
2 old
3 new
3 old

i tried select * from tableA where Status not in ('closed')
and I get as below:

ID status
1 new
1 old
2 new
2 old
3 new
3 old

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-11 : 05:00:04

do you mean this then?


SELECT t.* 
FROM Table t
INNER JOIN (SELECT ID
            FROM Table
            GROUP BY ID
            HAVING SUM(CASE WHEN status='closed' THEN 1 ELSE 0 END)=0
           )t1
On t1.ID = t.ID

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

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-07-11 : 05:21:12

I meant those with 'closed' status will not grab the whole ID.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-11 : 05:25:26

quote:
Originally posted by peace

I meant those with 'closed' status will not grab the whole ID.

Thats exactly what suggestion i gave does. Did you try it?

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

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-07-11 : 05:37:41

Ya i tried.
But the id with 'closed' still included.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-11 : 05:48:42

quote:
Originally posted by peace

Ya i tried.
But the id with 'closed' still included.

it wont if you use it as suggested

show your code plese

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

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-07-11 : 05:56:47

sorry it works fine now.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-11 : 05:58:24

quote:
Originally posted by peace

sorry it works fine now.

Thanks

So what went wrong last time?

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