By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,995 Members | 1,566 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,995 IT Pros & Developers. It's quick & easy.

Need help with Query

P: 3
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
Aug 5 '07 #1
Share this Question
Share on Google+
1 Reply


ck9663
Expert 2.5K+
P: 2,878
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
Aug 6 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.