Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have 2 tables: records and access_rights

Access table has list of permission for certain and should be used to filter records based on rules:

  • function field is mandatory
  • object field is optional. if it`s empty do not apply filter else filter by value All rules should work in one query. Please see example of expected output and SQL Fiddle data.

    | ID |   FUNCTION |   OBJECT | SUM |
    |----|------------|----------|-----|
    |  1 | Function 1 | Object 1 | 900 |
    |  4 | Function 1 | Object 2 | 750 |
    |  5 | Function 2 | Object 2 | 200 |
    

Thanks in advance!

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE records (
  [id] int NOT NULL PRIMARY KEY,
  [function] varchar(100),
  [object] varchar(100),
  [sum] money
  );

INSERT INTO records VALUES
(1, 'Function 1', 'Object 1', 900),
(2, 'Function 2', 'Object 1', 600),
(3, 'Function 3', 'Object 1', 800),
(4, 'Function 1', 'Object 2', 750),
(5, 'Function 2', 'Object 2', 200),
(6, 'Function 3', 'Object 2', 500);

CREATE TABLE access_rights ( 
  [id] int NOT NULL PRIMARY KEY,
  [user] varchar(50),
  [function] varchar(100),
  [object] varchar(100)
  );

INSERT INTO access_rights VALUES
(1, 'User', 'Function 1', ''),
(2, 'User', 'Function 2', 'Object 2');

Query 1:

SELECT * FROM records WHERE 
[records].[function] in 
(SELECT "function" FROM access_rights where "user"='User'  )

Results:

| ID |   FUNCTION |   OBJECT | SUM |
|----|------------|----------|-----|
|  1 | Function 1 | Object 1 | 900 |
|  2 | Function 2 | Object 1 | 600 |
|  4 | Function 1 | Object 2 | 750 |
|  5 | Function 2 | Object 2 | 200 |
share|improve this question

2 Answers 2

up vote 3 down vote accepted

I'd recommend reading Erland Sommarskog's "Dynamic Search Conditions" post here

In your case, here's a fiddle with the pattern: SQL Fiddle

For posterity, here's the code:

SELECT * 
FROM records r
WHERE 
EXISTS (
  SELECT 1 
  FROM access_rights a
  WHERE r.[function] = a.[function]
  and a.[user] = 'user'
  AND (A.[object] = r.[object] or a.[object] = ''))

Also, please stop using reserved words as column names.

share|improve this answer
    
I think you actually want to add the object filter to your subQ as well –  JNK Mar 7 at 15:22
    
Thanks. I actually didn't recognize that as a req. –  swasheck Mar 7 at 15:25
    
You want it to be `AND (a.user = @user AND (A.object = r.object or a.object = '')) –  JNK Mar 7 at 15:26
    
He says null but I think the actual requirement is if no object is defined the user has access to all the objects for that function. –  JNK Mar 7 at 15:26
    
aw crap. i really should have read the actual question instead of assuming ... hang on –  swasheck Mar 7 at 15:30

You can separate this into two queries, then UNION the results together.

 SELECT *
   FROM records
  WHERE [function] IN
        (SELECT [function]
           FROM access_rights
          WHERE [object] = '')

UNION ALL

 SELECT R.*
   FROM records AS R
        INNER JOIN access_rights AS AR
                   ON AR.[function] = R.[function] AND AR.[object] = R.[object];
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.