Need help with Query

[CoolBreeze812]

Hi, I need help with a query and I'm having trouble getting it formed correctly. I have 3 tables: users, authorization_users, and reports.

The user table contains a bunch of users with fields like userID, first_name, last_name, and such.

The authorization_users 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_users, 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_GetReportPermissions
@username varchar
AS
SELECT rpt.reportName, au.[Value], us.UserName
FROM dbo.authorization_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

[ck9663]

Hi, I need help with a query and I'm having trouble getting it formed correctly. I have 3 tables: users, authorization_users, and reports.

The user table contains a bunch of users with fields like userID, first_name, last_name, and such.

The authorization_users 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_users, 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_GetReportPermissions
@username varchar
AS
SELECT rpt.reportName, au.[Value], us.UserName
FROM dbo.authorization_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

how about this:

SELECT rpt.reportName, au.[Value], us.UserName
FROM dbo.authorization_users au, dbo.reports rpt, dbo.users us
WHERE us.UserName = @username AND au.UserID =* us.UserID AND rpt.PermID =* au.PermID