aspnet_Roles and aspnet_usersinroles

[pagambar]

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.

[deepuv04]

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

[pagambar]

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