Applying SQL WHERE Conditionally

Question

TABLE A
-------
USER ACCESS    RECORD_IDN  
1    ALL       NULL
2    PARTIAL   1
2    PARTIAL   2
3    PARTIAL   5

TABLE B
-------
NAME     FOLDER_ACCESS    R_IDN
FOLDER1  ALL
FOLDER2  ALL
FOLDER3  PARTIAL          5
FOLDER4  PARTIAL          1
FOLDER5  PARTIAL          2
FOLDER5  ALL

WHEN USER 1 Logs IN he has no restriction I should SHOW Folder1-5 .

WHEN USER 2 Logs IN he should NOT see FOLDER 3 (ALL AND 1,2 is fine for view)

WHEN USER 3 Logs IN he should see FOLDER 1, FOLDER 2 , FOLDER3, FOLDER5

The thing is I need to have a WHERE clause conditionally like this :

SELECT DISTINCT NAME * FROM TABLE B   

PSEUDO CODE 

IFF USER_LOGGED IN HAVING 'ALL' in TABLE A 

DO NOTHING 

ELSE IFF ITS PARTIAL 

WHERE FOLDER_ACESS IS "ALL" OR R_IDN in (TABLEA.R_IDN FOR HIM) .

Is such construcuts possible in SQL ? Its more for an academic understanding of what is possible and what is not . I know to solve this query by breaking it seperately , however want to see if there are tricky solutions for this . And if its worth pursing single hit tricky solutions .

Targetted platforms are ORACLE and MSSQL . However any SQL platform solutions are welcome .

What RDBMS are you using? SQL Server? This is similar: http://stackoverflow.com/questions/206484/sql-switch-case-in-where-clause — Andrew, Jun 17 '14 at 17:53
Andrew its a bit different as in the @location is not user input , it should be computer from another table . — Nishant, Jun 17 '14 at 18:01

score 2 · Accepted Answer · answered Jun 17 '14 at 18:01

2

This would do it - you just need to supply the user you want the results for in the where clause. E.g. for user 2:

SELECT distinct a.user, b.name
FROM TABLEA a
INNER JOIN TABLEB b ON a.record_idn = b.r_idn 
                    OR a.access = 'ALL' 
                    OR b.folder_access = 'ALL'
WHERE a.user = 2
ORDER BY a.user, b.name

answered Jun 17 '14 at 18:01

StevieG

8,639
23
31

Interesting . For the above example say User = 1 - how does the join work ? Is it applied systematically by iterating over each row in B and by checking all the 3 conditions together and then if one satisfies , attach it ? – Nishant Jun 17 '14 at 18:16
1

Not really, it returns all rows that match any of the conditions (because they're OR'd). So for User1, the first condition has no matches. The second condition does match, so ALL rows from b are returned. The third condition would also match folders 1, 2 and 5, so would return them as well. The DISTINCT removes the duplicates. If you remove the DISTINCT, you'll see what I mean.. – StevieG Jun 18 '14 at 08:40
Thanks StevieG , I was trying to understand whats happening inside during the match . That makes sense . Overall you are solving a WHERE problem with JOIN . – Nishant Jun 18 '14 at 12:46

hgulyan · Answer 2 · 2014-06-17T18:03:55.550

1

Is this what you're trying to achieve?

SELECT NAME
FROM B 
WHERE EXISTS (
   SELECT *
   FROM A
   WHERE A.USER = @userId 
      AND (A.ACCESS = 'ALL' 
             OR B.FOLDER_ACCESS = 'ALL'
             OR (A.ACCESS = 'PARTIAL' AND A.RECORD_IDN = B.R_IDN)
          )
)
GROUP BY NAME

For MS SQL there's an option to use CROSS APPLY

SELECT NAME
FROM B 
  CROSS APPLY (
      SELECT 1
      FROM A
      WHERE A.USER = @userId 
      AND (A.ACCESS = 'ALL' 
             OR B.FOLDER_ACCESS = 'ALL'
             OR (A.ACCESS = 'PARTIAL' AND A.RECORD_IDN = B.R_IDN)
          )
)
GROUP BY NAME

edited Jun 17 '14 at 18:03

answered Jun 17 '14 at 17:53

hgulyan

8,099
8
50
75

Thanks will lookup Exists and Cross Apply – Nishant Jun 17 '14 at 18:08

Applying SQL WHERE Conditionally

2 Answers2