Jump to content

Access to SQL


DaNuGai

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

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...