CODEWITHSUNDEEP
sqloracle-database
sonal
sonal

Reputation: 101

Find exact match in a table in oracle using sql statements

i have a situation where i need to insert a record only when the parameters passed in do not have exact match in table. For e.g., i have a Role and a permission table:

Role:
-------
R1  
R2  
R3  


Permission:
-----------
P1  
P2  
P3

then there is a Role_Perm table:

Role|Permission:
----------------
R1 | P1   
R2 | P1  
R2 | P2 
R2 | P3  
R3 | P1
R3 | P3

Now the problem in hand is that i want to insert a new record in the Role_Perm table when the exact combination of permissions (given for a new role) is not already present in table; for eg. 'R5 | P1, P2' should be inserted but 'R5 | P1, P3' should not be inserted.

I want to find if this combination already exists using a sql query before performing an insert operation. I need help with the query. Any help is highly appreciated.

Upvotes: 0

Views: 750

Answers (2)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

You could use this query at first:

with ins as (select column_value cv from table (sys.odcivarchar2list('P9', 'P1')))
select count(1) cnt 
  from (select listagg(cv, ',') within group (order by cv) list from ins) 
  join (select listagg(permission, ',') 
                       within group (order by permission) list 
          from role_perm group by role) using (list)

SQLFiddle demo

... and if it returns 0 - perform insert. In first line put permissions which should be checked.

Upvotes: 1

janaka aravinda
janaka aravinda

Reputation: 124

First of all These tables are not normalized,if you have the permission to change table structure, you have to consider following 01 Single column (in single row) should not have multiple values 02 So You have to create table with two columns Role, Permission with composite Primary Key (use primary key for both columns)

Then you can't input both values in twice eg:- we entered R2 | P2 then you can enter R2 | P3 but You cant enter R2 | P2

simply Row cant be duplicate in this strucure

Upvotes: 0

Related Questions