CODEWITHSUNDEEP
sqlwhere-clause
Nishant
Nishant

Reputation: 21934

Applying SQL WHERE Conditionally

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 .

Upvotes: 1

Views: 226

Answers (2)

hgulyan
hgulyan

Reputation: 8249

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

Upvotes: 1

StevieG
StevieG

Reputation: 8729

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

Upvotes: 2

Related Questions