Need urgent help with the query

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

SQL Server 2008 Forums

Transact-SQL (2008)

Need urgent help with the query

Author

Topic

ssunny
Posting Yak Master

133 Posts

Posted - 2012-05-30 : 16:06:03

Hello Guys,

I need some urgent help.

Here's the table with some sample data:

create table test_insert
(
mid int null,
mname varchar (500) null,
nid int null,
total int null
)

insert into test_insert
values (1,'NULL',0,230)

insert into test_insert
values (1,'test',111,230)

insert into test_insert
values (2,'test1',222,567)

insert into test_insert
values (3,'NULL',0,657)
go

select * from test_insert give me

mid mname nid total
1 NULL 0 230
1 test 111 230
2 test1 222 567
3 NULL 0 657

Expected output:

mid mname nid total
1 test 111 230
2 test1 222 567
3 NULL 0 657

In above case I have more than one row for mid 1 and with nid 0 and 111 so i don't want to show row with nid 0. So condition would be : don't show a record where nid = 0 only if there is already a record where nid > 0 for the same mid.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-30 : 16:09:10

[code]
SELECT mid, mname, nid, total
FROM
(
SELECT *,SUM(CASE WHEN nid >0 THEN 1 END) OVER ( PARTITION BY mid) AS Occ
FROM table
)t
WHERE Occ =0
OR nid >0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ssunny
Posting Yak Master

133 Posts

Posted - 2012-05-30 : 16:15:39

Hi Visakh,

Thanks for the reply but your query gives me

mid mname nid total
1 test 111 230
2 test1 222 567

I want

mid mname nid total
1 test 111 230
2 test1 222 567
3 NULL 0 657

since there is only one record with mid = 3 I want that too.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-30 : 16:33:49

quote:
Originally posted by ssunny

Hi Visakh,

Thanks for the reply but your query gives me

mid mname nid total
1 test 111 230
2 test1 222 567

I want

mid mname nid total
1 test 111 230
2 test1 222 567
3 NULL 0 657

since there is only one record with mid = 3 I want that too.

Thanks.

sorry there was a typo



SELECT mid, mname, nid, total
FROM
(
SELECT *,SUM(CASE WHEN nid >0 THEN 1 ELSE 0 END) OVER ( PARTITION BY mid) AS Occ
FROM #test_insert
)t
WHERE Occ =0
OR nid >0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ssunny
Posting Yak Master

133 Posts

Posted - 2012-05-30 : 16:39:47

Perfect. Thank you sir.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-30 : 17:17:03

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources