Query Help

Question

Query Help

thanks19

SSC Rookie

Points: 28
More actions
February 2, 2011 at 10:33 am

#244521

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 query RolesByUser where UserId is provided dynamically through a parameter @userid
SELECT UsersInRoles.RoleId
FROM UsersInRoles
WHERE 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.RoleName
FROM Roles LEFT JOIN RolesByUser
ON Roles.RoleId = RolesByUser.RoleId;
The result would look similar to a sample below (minus "..."):
isUserInRole..........RoleId..........RoleName
...Y...........................1.................Supervisor
...N...........................2.................Manager
Thanks in advance

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

great job on your first post providing lots of details..thank you!

This is fairly straight forward; the IIF is really equivalent to a CASE statement like this; you provided everything else, what with the sample you posted:

SELECT

CASE

WHEN RolesByUser.RoleId Is Null

THEN 'N'

ELSE 'Y'

END AS isUserInRole,

Roles.RoleId,

Roles.RoleName

FROM Roles LEFT JOIN RolesByUser

ON Roles.RoleId = RolesByUser.RoleId

--is this needed for a specific user? without it it shows all users.

--WHERE UsersInRoles.UserId=@UserId;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

thanks19 SSC Rookie Points: 28 More actions · Answer 2

Thanks for the reply. The only issue is RolesByUser is not a table. It's a query I created in access.

In other words, I need to merge the two queries I have listed above using UsersInRoles and Roles tables.

The whole point of doing this is to list all the Roles in the database with a boolean (Y/N) column to indicate the roles assigned to a particular user.

thanks19 SSC Rookie Points: 28 More actions · Answer 3

I found a solution. 😀

Thank you so much for your help