[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query

Posted on 2011-05-03
13
Medium Priority
?
319 Views
Last Modified: 2012-05-11
This suggestion which I had accepted is not working if the user is a admin. He has full access to all the Locations, Customers and their contacrs. But I don't see all the contracts. Because of the LEFT JOIN with Users ON UserID with OH.UserID.

Can someone please advice me?

Thanks,
-B
0
Comment
Question by:BrookK
11 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35561077
If you wouldn't have permissions on table, then you would have received error that you don't have SELECT permission on the table.
Since you are not able to see few records, there is some other issue with your query..
Post your query to see how it can be fixed..
0
 
LVL 9

Expert Comment

by:sarabhai
ID: 35644342
Please show the query and specify user permission if permission issue.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35688652
No one can set the Table specific permissions.

Its problem related to SQL logic. (Filter or join related)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:BrookK
ID: 35688914
Permissions are set for the users in the tables. Please see the question ID 26953776 for the sample table structure and suggested query.

This query doesn't work for admin users though.

Thanks,
-B
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35692194
>> This query doesn't work for admin users though.

Then I hope it is not working for anyone..
Can you post your expected result set so that we can try out some other logics..
0
 

Author Comment

by:BrookK
ID: 35693097
The actual query and sample table and data structure is on question ID 26953776.

Thanks,
-B
0
 

Author Comment

by:BrookK
ID: 35697642
This is not the SQL permissions as admin. It is the internal role called admin which is determined by a flag in a user table. Please see the question 26953776 for more details
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35714118
Can someone post a real link to this elusive ID 26953776
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35714181
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 35715823
>> It is the internal role called admin which is determined by a flag in a user table. Please see the question 26953776 for more details

Seems like attached here is the Final query you thought it worked.
If so, then you need to have admin user have some orders in OrdHistory table.
Since it is a LEFT JOIN, if you don't have any entries in OrdHistory table, your query will not work for user named admin. Kindly let me know whether your query is working for all other tables.

And try changing LEFT JOIN to INNER JOIN to get it worked for admin user..
SELECT OH.OrdNum, OH.Location, OH.Customer, OH.OrderContract
  FROM OrdHistory AS OH 
       LEFT OUTER JOIN UserLocation AS UL 
               ON OH.Location = UL.Location 
       LEFT OUTER JOIN UserCust AS UC 
               ON UL.UserID = UC.UserID AND OH.Location = UC.Location
                                        AND OH.Customer = UC.Customer
       LEFT OUTER JOIN UserCont AS UCO
               ON UC.UserID = UCO.UserID AND OH.Location = UCO.Location 
                                         AND OH.Customer = UCO.Customer
                                         AND OH.OrderContract = UCO.AllCont

Open in new window

0
 

Author Closing Comment

by:BrookK
ID: 36106490
helpful
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question