Exclusive Join - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Exclusive Join

Author

Topic

vbArch
Starting Member

5 Posts

Posted - 2006-05-23 : 13:50:57

I have a link table between a user table and a lookup that contains the following data

ID Role
----------- -----------
92166 1
92166 11
92167 7
92167 11
92210 3
92210 7
92210 11
92211 7
92211 11
92212 7
92212 11
92213 7
92213 11

92213 3

The Link table stores whether a user has a combination of rolea. I need to be able to ask for any given user IE 92210 has the roles 3 and 7 any additional roles are ok but they must have all of the combinations I am asking for. The role combinations can be anywhere from 1 to 40. So in one case i have to ask for roles (3,7) and in another (1,7,11) The only way I can figure out how to do this is a dynamic self join or by using a cursor (yuck).

This is a legacy application and the requirement changed from roles being inclusive to exclusive and a table structure change is out of the question.

There must be a better way. Any help would be appreciated.

Thanks in advance

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-23 : 16:24:27

Put the roles into a table and the query you want is

select id
from tbl
join roles
on tbl.role = roles.role
group by id
having count(*) = (select count(*) from roles)

You can use http://www.nigelrivett.net/SQLTsql/ParseCSVString.html or something similar to get the table of roles from a csv string.

otherwise
select id
from tbl
where @roles like '%,' + convert(varchar(20),role) = ',%'
group by id
having count(*) = len(@roles) - len(replace(@roles,',','')) + 1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-24 : 02:47:55

select id
from tbl
where @roles like '%,' + convert(varchar(20),role) + ',%'
group by id
having count(*) = len(@roles) - len(replace(@roles,',','')) + 1

Madhivanan

Failing to plan is Planning to fail

vbArch
Starting Member

5 Posts

Posted - 2006-05-24 : 12:34:41

Thanks, I got it working, appreciate the help you saved me days

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources