Hi, I need help with a query and I'm having trouble getting it formed correctly. I have 3 tables: users, authorization_u sers, and reports.
The user table contains a bunch of users with fields like userID, first_name, last_name, and such.
The authorization_u sers table contains a permID which is the key from the reports table, userID, and a Value (bit)
The Reports table contains a permID, reportName, and a few other fields.
I'm trying to construct a Stored Proc that will accept a username as a parameter to query the users, authorization_u sers, and reports table and return all reports that a single user has displaying the report name and it's associated value (true/false).
Here is what I have
CREATE PROC dbo.sp_GetRepor tPermissions
@username varchar
AS
SELECT rpt.reportName, au.[Value], us.UserName
FROM dbo.authorizati on_users, dbo.reports, dbo.users
WHERE us.UserName = @username AND au.UserID = us.UserID AND rpt.PermID = au.PermID
Can someone help me to form this correctly to obtains the results mentioned above?
Trying to get results in the form of:
UserID UserName Report Value
1 JoeUser MarketingRpt 1
1 JoeUser FinanceRpt 0
1 JoeUser SalesRpt 1
The user table contains a bunch of users with fields like userID, first_name, last_name, and such.
The authorization_u sers table contains a permID which is the key from the reports table, userID, and a Value (bit)
The Reports table contains a permID, reportName, and a few other fields.
I'm trying to construct a Stored Proc that will accept a username as a parameter to query the users, authorization_u sers, and reports table and return all reports that a single user has displaying the report name and it's associated value (true/false).
Here is what I have
CREATE PROC dbo.sp_GetRepor tPermissions
@username varchar
AS
SELECT rpt.reportName, au.[Value], us.UserName
FROM dbo.authorizati on_users, dbo.reports, dbo.users
WHERE us.UserName = @username AND au.UserID = us.UserID AND rpt.PermID = au.PermID
Can someone help me to form this correctly to obtains the results mentioned above?
Trying to get results in the form of:
UserID UserName Report Value
1 JoeUser MarketingRpt 1
1 JoeUser FinanceRpt 0
1 JoeUser SalesRpt 1
Comment