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]

Go to Top of Page

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/
Go to Top of Page
   

- Advertisement -