Query Help with null - 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

Query Help with null

Author

Topic

gfaryd
Starting Member

27 Posts

Posted - 2010-05-20 : 05:02:21

I have following table structure i m using sql 2000

create table mytab
(pid varchar(10),
a_check char(1),
b_check char(1)
detail varchar(30))

it has following data

insert into mytab values ('abc1','A','A','good')
insert into mytab values ('abc1',null,null,'')
insert into mytab values ('abc2','A','B','good')
insert into mytab values ('abc2',null,null,'')
insert into mytab values ('abc3','A','A','good')
insert into mytab values ('abc3','A','B','Fair')

what i want in result those PID where value of both a_check and b_check is not null in all rows

like in above data set i want only pid values to be return
is

abc3

i dont want abc1 and abc2 in result as one row of them contains null

Regards

only those values return where

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-20 : 05:05:57

[code]
select *
from mytab t
where not exists
(
select *
from mytab x
where x.pid = t.pid
and (
x.a_check is null
or x.b_check is null
)
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-05-20 : 05:39:56

If SQL 2005 +

Try this

Select * from (
Select *,row_number() over( partition by pid order by pid) as rank_no
from mytab where a_check is not null and b_check is not null) a
where rank_no>1

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources