not too complex query WITH IN - 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 2005 Forums

Transact-SQL (2005)

not too complex query WITH IN

Author

Topic

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2011-07-07 : 06:04:19

I have following tables

userroles UR
id userid roleid

documentrole DR
id roleid doctype

documenttype DT
DocType ( is id ), description and other not relevant fields

What im trying to accomplish is writing a query that gives all doctypes which havent got a role coupled to it

PLUS

the ones which DOES have role(s) coupled to it...
note : when a doctype is coupled to 2 roles, only
users which have BOTH those roles, may view it

the only parameter i get is the userid..

so far i got this :

SELECT 1,DR.*,DT.* 

FROM  eMK_DocumentType DT

LEFT OUTER JOIN eMK_Def_Codes DC
ON DC.ID = DT.DocType

LEFT OUTER JOIN eMK_DocumentRole DR
ON DR.DocType = DT.DocType

AND (DR.RoleID is NULL OR
		(
			DR.RoleID IN	( 
					SELECT distinct UR.RoleID 
					FROM aspnet_UsersInRoles UR
					
					eMK_DocumentRole DR
					INNER JOIN eMK_DocumentType DT2
					ON DT2.Doctype = DR.DocType
					
					WHERE UR.UserId = '0fbff8cf-c53c-4d0e-ae65-040b36a8baeb'
							)
		)
	)

the use of IN, is clearly not right... but if i could use an IN, wich only return true when ALL the strings are found.. this would fix it i guess

all comments are welcome
thx in advance

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2011-07-07 : 08:40:30

so far i got this :)

select @count = count(*)
	--DR.RoleID,UR.RoleID

	FROM eMK_DocumentRole DR
	LEFT OUTER JOIN 
	(SELECT * FROM aspnet_UsersInRoles 
	WHERE 
	UserId = @userid
	--UserId = '0fbff8cf-c53c-4d0e-ae65-040b36a8baeb' --user1 -> role1
	--UserId = '7f8b4023-ca7c-4f56-b033-ef2ba30e24f5'-- user2 ->role1 en role2
	)UR
	ON DR.RoleID = UR.RoleID
	WHERE DR.Doctype = @doctype
	AND UR.RoleID IS NULL;

	IF @count > 0
		SELECT @bit = CAST(0 as bit);
	ELSE
		SELECT @bit = CAST(1 as bit);

	RETURN @bit;

this function return if a user may 'see' that doctype or not

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 09:11:50

if exists
(
select *
from eMK_DocumentRole dr
left join aspnet_UsersInRoles ur
on dr.RoleID = ur.RoleID
and ur.UserId = @userid
where dr.Doctype = @doctype
and ur.RoleID is null
)
select @bit = 0
else
select @bit = 1

note - if there are no roles asssociated with a document then all users will be able to see it.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources