Exclusive Join

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

You could do something like joining to the passed array with using a split function which is wrote sometime ago:

CREATE FUNCTION dbo.Split
(
@.String VARCHAR(200),
@.Delimiter VARCHAR(5)
)
RETURNS @.SplittedValues TABLE
(
OccurenceId SMALLINT IDENTITY(1,1),
SplitValue VARCHAR(200)
)
AS
BEGIN
DECLARE @.SplitLength INT

WHILE LEN(@.String) > 0
BEGIN
SELECT @.SplitLength = (CASE CHARINDEX(@.Delimiter,@.String) WHEN 0 THEN
LEN(@.String) ELSE CHARINDEX(@.Delimiter,@.String) -1 END)

INSERT INTO @.SplittedValues
SELECT SUBSTRING(@.String,1,@.SplitLength)

SELECT @.String = (CASE (LEN(@.String) - @.SplitLength) WHEN 0 THEN ''
ELSE RIGHT(@.String, LEN(@.String) - @.SplitLength - 1) END)
END
RETURN
END

This evaluates to:

DELCARE @.SomeValues VARCHAR(100)
SET @.SomeValues = '1,7,11'

SELECT 'Access Granted'
FROM SomeTable ST
INNER JOIN dbo.Split(@.SomeValues,',') S ON
ON S.SplitValue = ST.Role
WHERE ID = 95321
HAVING COUNT(*) = (SELECT COUNT(*) FROM dbo.Split(SomeValues,','))

This is untested and maybe need to be modified, try to use that in the case you don′t wanna use dynamic sql.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks, that put me on the right track and now have a udf that does the job

sql