Vinuth Kumar
asked on
Retrive values based on comma sepearted
I have table
USER_ID ROLE_ID
1 11,32,44
2 34, 11, 32, 44
3 11
4 44, 22, 32
5 32
6 32, 44
If i pass 11, 32 role_id in where condition then it should fetch column which contains 11 and 32 , only 11 or only 32 should not be fetched , whatever role_id i pass all should be present, please help me with query oracle , it will really helpful for me
USER_ID ROLE_ID
1 11,32,44
2 34, 11, 32, 44
3 11
4 44, 22, 32
5 32
6 32, 44
If i pass 11, 32 role_id in where condition then it should fetch column which contains 11 and 32 , only 11 or only 32 should not be fetched , whatever role_id i pass all should be present, please help me with query oracle , it will really helpful for me
ASKER
but exactly how many roleIds will be unkonown, it depends upon user, so 10 differnt role Id i may get and i should fetch depending on that
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi geert i do have user table and user roles table, but when admin assigns multiple roles then i will stored in that manner which i have retirve for some other purpose
their i finding it difficult sir
thanx for your valuable time you spent sir
their i finding it difficult sir
thanx for your valuable time you spent sir
ASKER
Thanks for your valuable suggestions guys
ASKER
Thanx a lot geert, indeed its a bad design, we decided to handle in front end instead in query, your feedback save us cracking our head and wasting time
select * from your_table a where instr(a.ROLE_ID,'11')<>0 and instr(a.ROLE_ID,'32')<>0;
It is only a tip I think that there are also another solutions :).