DaNuGai Posted February 2, 2011 Share Posted February 2, 2011 I have a couple of queries in Access database that does exactly what I want. However, I want to convert this into an SQL Query but I'm not quite sure how I can go about doing that.Here is my First Ruery RolesByUser where UserId is provided dynamically through a parameter @UserId SELECT UsersInRoles.RoleIdFROM UsersInRolesWHERE UsersInRoles.UserId=[Enter UserId]; Here is Second Query that uses the query above: SELECT IIf(RolesByUser.RoleId Is Null,"N","Y") AS isUserInRole, Roles.RoleId, Roles.RoleNameFROM Roles LEFT JOIN RolesByUser ON Roles.RoleId = RolesByUser.RoleId; The result would look similar to a sample below (minus "..."):isUserInRole.....RoleId.....RoleName=======================Y.....................1............SupervisorN.....................2............ManagerThanks in advance Link to comment Share on other sites More sharing options...
DaNuGai Posted February 2, 2011 Author Share Posted February 2, 2011 Found a solution :-D SELECT CASE WHEN RolesByUser.RoleId Is Null THEN 'N' ELSE 'Y' END AS isUserInRole, Roles.RoleId, Roles.RoleNameFROM Roles LEFT JOIN (SELECT UsersInRoles.RoleId FROM UsersInRoles WHERE UsersInRoles.UserId=@UserId) AS RolesByUser ON Roles.RoleId = RolesByUser.RoleId Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.