By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,575 Members | 1,303 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,575 IT Pros & Developers. It's quick & easy.

aspnet_Roles and aspnet_usersinroles

P: 2
I have a complex query which doesn't return what I need using outer joins.

The query i've tried is:

SELECT dbo.User_Roles_Views.UserId, dbo.Roles_view.RoleName
FROM dbo.User_Roles_Views RIGHT OUTER JOIN
dbo.Roles_view ON dbo.User_Roles_Views.RoleName = dbo.Roles_view.RoleName
WHERE (dbo.User_Roles_Views.UserId = 'e69f320b-f1da-480e-9c54-9fa0680299d6')

WHat I am trying to return is the Roles in which the user belongs to and the roles in which he doesn't (Null Rows).

Role UserID
Admin e69f320b-f1da-480e-9c54-9fa0680299d6
Guest NULL
OUtput e69f320b-f1da-480e-9c54-9fa0680299d6
Status null

You get the idea!

I can then use this to populate a checkboxlist and do stuff with it. I could just call the aspnet_roles in the checkboxlist then iterate through the items and set checked state by using the data - but that would be too easy.
Jan 24 '08 #1
Share this Question
Share on Google+
2 Replies


deepuv04
Expert 100+
P: 227
I have a complex query which doesn't return what I need using outer joins.

The query i've tried is:

SELECT dbo.User_Roles_Views.UserId, dbo.Roles_view.RoleName
FROM dbo.User_Roles_Views RIGHT OUTER JOIN
dbo.Roles_view ON dbo.User_Roles_Views.RoleName = dbo.Roles_view.RoleName
WHERE (dbo.User_Roles_Views.UserId = 'e69f320b-f1da-480e-9c54-9fa0680299d6')

WHat I am trying to return is the Roles in which the user belongs to and the roles in which he doesn't (Null Rows).

Role UserID
Admin e69f320b-f1da-480e-9c54-9fa0680299d6
Guest NULL
OUtput e69f320b-f1da-480e-9c54-9fa0680299d6
Status null

You get the idea!

I can then use this to populate a checkboxlist and do stuff with it. I could just call the aspnet_roles in the checkboxlist then iterate through the items and set checked state by using the data - but that would be too easy.
probably this will help

SELECT v1.UserId, rv.RoleName
FROM dbo.Roles_view as RV LEFT OUTER JOIN
(SELECT UserId,RoleName FROM dbo.User_Roles_Views
WHERE UserId = 'e69f320b-f1da-480e-9c54-9fa0680299d6' ) AS v1 ON
RV.RoleName = V1.RoleName
Jan 24 '08 #2

P: 2
Cheers for the reply - Although it doesn't do exactly what I want but I went with

SELECT
a.itemname,
b.userid,
CONVERT(bit, CASE WHEN USERID IS NULL THEN 0 ELSE 1 END) AS Item
FROM
dbo.cbx_Item_1 AS a LEFT OUTER JOIN dbo.cbx_user_items AS b ON a.itemname = b.itemName
AND
b.userid = @userid


Cheers
Jan 27 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.