Jump to content

Access to SQL


DaNuGai
 Share

Recommended Posts

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

Edited by DaNuGai
Link to comment
Share on other sites

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...