Query Help! - 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!

Author

Topic

gfaryd
Starting Member

27 Posts

Posted - 2011-04-15 : 05:49:07

Dear All

I have following table structure in sql 2000

create table test (
pid int,
og_id int,
remarks varchar(20)
)

sample data

insert into test values (1,33,null)
insert into test values (1,33,'account')
insert into test values (1,44,null)

insert into test values (2,13,null)
insert into test values (2,31,'account')
insert into test values (2,31,'something')
insert into test values (2,31,null)
insert into test values (2,31,null)

in my scenario i want following out put

pid og_id remarks
1 33 account
1 44
2 13
2 31 account
2 31 something

if remarks is null against same pid and og_id like in case of pid 1 and og_id 33 another row exist with pid 1 and og_id with remarks 'account' , i only need row without null. in case of pid 1 and og_id 44 remarks is null and no row exist having some remarks against these pid and og_id.

so in short i want to omit those rows in which null remarks exist if another row is exist with not null

so the output will be.

1 33 account
1 44

any help in query

Regards

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-04-15 : 06:03:39

quote:

in my scenario i want following out put

pid og_id remarks
1 33 account
1 44
2 13
2 31 account
2 31 something


with cte as
(
select pid, og_id, remarks from test where remarks is not null
union all
select pid, og_id, remarks 
from test t
where not exists
	(select pid, og_id, remarks 
	 from test t1  
	 where t.pid=t1.pid and t.og_id=t1.og_id and remarks is not null)
)select pid, og_id, remarks from cte order by pid

--Ranjit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources