selecting records that not exists in group

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)

selecting records that not exists in group

Author

Topic

Auris
Starting Member

7 Posts

Posted - 2012-04-04 : 04:17:00

Hi there, so my question is fairly simple i guess but i cant figure out how to do it properly. So here is the data sample

Data Sample

UniqueId GroupId RoleId
1 A R1
2 A R2
3 A R8
4 B R8
5 B R5
6 B R1
7 B R2
8 C R2
9 C R3
10 D R1

So uniqueId is primary key and its unique, groupId is just group the records belongs to and RoleId determines which roles have access to particular group. My goal is to select all groups that doesnt have role of R8 assigned to them? no how do i acomplish that ? This is urgent! please provide me with possible approach.

Life is hard. It's even harder if you're stupid.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-04 : 04:22:48

[code]
select distinct GroupId
from [data sample] s
where not exists
(
select *
from [data sample] x
where x.GroupId = s.GroupId
and x.RoleId = 'RB'
)
[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-04 : 18:34:04

simply this?


select GroupId
from   [data sample] 
group by GroupId
having count(case when RoleId = 'R8' then 1 else null end) =0

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

Auris
Starting Member

7 Posts

Posted - 2012-04-05 : 03:49:05

this ones an interesting approach, thank you for you answers guys.

quote:
Originally posted by visakh16

simply this?

select GroupId
from [data sample]
group by GroupId
having count(case when RoleId = 'R8' then 1 else null end) =0

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

Life is hard. It's even harder if you're stupid.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 12:30:44

welcome

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

raj.prabhu001
Starting Member

16 Posts

Posted - 2012-04-07 : 05:06:10

select GroupId
from [data sample]
group by GroupId
having count(case when RoleId = 'R8' then 1 else null end) =0

can you plz explain how does

having count(case when RoleId = 'R8' then 1 else null end) =0

will not return roleid = r8

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-08 : 18:11:43

it will not return roleid=r8 but it will returns all the groups (groupid values) where RoleId='R8' was not involved in it.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources