CODEWITHSUNDEEP
sqldb2
Mohd Faizal Mohd Zahari
Mohd Faizal Mohd Zahari

Reputation: 3

How do I search IN condition from column?

I have create two tables and named it ROLES and ACCESS_CONTROL. Each ACCESS_CONTROL user can have many ROLES in ALLOWABLE_ROLES separated by comma.

ROLES

ROLE_ID     ROLE_NAME   
P001        ROLE 1      
P002        ROLE 2      
P003        ROLE 3      
P004        ROLE 4      
P005        ROLE 5      
P006        ROLE 6      
P007        ROLE 7      
P008        ROLE 8      
P009        ROLE 9      
P010        ROLE 10     
P011        ROLE 11     
P012        ROLE 12

ACCESS_CONTROL

USER_ID     USER        ALLOWABLE_ROLES                 
A001        MASTER      P009,P011,P012,P010,P006,P005,P001      
A002        ADMIN 1     P010,P011,P012,P003,P004,P006,P008      
A003        ADMIN 2     P011,P012,P010,P006             
A005        ADMIN 3     P011,P012,P003,P007,P006,P009,P001,P005     
A006        USER 1      P011,P012,P001,P006             
A007        USER 2      P010,P011,P012,P003,P006,P001,P000,P002

I try to get list of ROLES that a user have by change the column ALLOWABLE_ROLES value from this P009,P011,P012,P010,P006,P005,P001 to this 'P009','P011','P012','P010','P006','P005','P001' using CONCAT in SQL.

SELECT
    ROLE_ID,
    ROLE_NAME
FROM
    ROLES
WHERE
    ROLE_ID IN (SELECT CONCAT('''',CONCAT(RTRIM(CHAR(REPLACE(ALLOWABLE_ROLES,',',''','''))),'''')) FROM ACCESS_CONTROL WHERE USER_ID = 'A001')

However result returns zero.

But when I chance the code to this,

SELECT
    ROLE_ID,
    ROLE_NAME
FROM
    ROLES
WHERE
    ROLE_ID IN ('P009','P011','P012','P010','P006','P005','P001')

the result output shows

RESULT

ROLE_ID     ROLE_NAME   
P001        ROLE 1      
P005        ROLE 5      
P006        ROLE 6      
P009        ROLE 9      
P010        ROLE 10     
P011        ROLE 11     
P012        ROLE 12

Is there any possible to get output directly from the column?

Upvotes: 0

Views: 64

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12314

SELECT R.*
FROM ROLES R
JOIN ACCESS_CONTROL A ON LOCATE(','||R.ROLE_ID||',', ','||A.ALLOWABLE_ROLES||',')<>0
WHERE A.USER_ID = 'A001'

+1 To Paul's comment.

Upvotes: 1

Loghman Barari
Loghman Barari

Reputation: 71

In MSSQL 2017 you can use STRING_SPLIT function:

SELECT ROLE_ID, ROLE_NAME FROM ROLES IN STRING_SPLIT WHERE ROLE_ID IN (select value from STRING_SPLIT(select top 1 ALLOWABLE_ROLES where USER_ID = 'A001'))

But, for before MS2016 you can use the function in which has been described on How to split a comma-separated value to columns

Upvotes: 0

Related Questions