CODEWITHSUNDEEP
sqloracle-databasejoindistinctunion
mystic007
mystic007

Reputation: 3

Multiple unions in Oracle Sql with no duplicates on specific column

So, I have a table:

id   place  user     role
____________________________
1    a      u1       emp       
2    a      u2       emp       
3    a      u3       smallBoss  
4    b      u4       emp
5    b      u5       emp
6    b      u6       smallBoss
7    b      u7       medBoss

Now, I want to create a view with these users and their bosses in additional column with these rules:

1 - if user do have smallBoss at his place, smallBoss is in boss column no matter what!!!

2 - if user doesn't have smallBoss at his place, then medBoss is in boss column if he is present, but if not then bigBoss is the boss

3 - obviously, smallBoss at place b will have medBoss in his boss column

4 - bigBoss is last option

Conclusion: bosses have some kind of reverse priorities:

1 - smallBoss - if he is present, he is surely boss for emp

2 - medBoss - if he is present, he is boss for all smallBoss and if there is no smallBoss at a place, then he is the boss for all emp

3 - 'bigBoss' - he is present everywhere, but should only be boss for medBoss and for emp where neither smallBoss nor medBoss is present.

I tried join versions and union. Union seems to be closest to my goal, but I have multiple users with different bosses (users u4 and u5 are doubled!!!). I want u4 and u5 to show just once with smallBoss in their boss column:

id   place  user     role          boss
___________________________________________
1    a      u1       emp           smallBoss
2    a      u2       emp           smallBoss
3    a      u3       smallBoss     bigBoss
4    b      u4       emp           smallBoss
5    b      u5       emp           smallBoss
6    b      u6       smallBoss     medBoss
7    b      u7       medBoss       bigBoss

Upvotes: 0

Views: 852

Answers (1)

user5683823
user5683823

Reputation:

You need neither joins nor unions; what you do need are analytic functions and case expressions. (In this solution, analytic max().)

As the query below shows, it would be a lot easier if you used a coding system for "roles" - 0 for employee, 1 for smallBoss, etc. As it is, we need to create it "on the fly" in the query.

USER and ROLE are reserved words in Oracle, they shouldn't be used as column names (not even as replacements for posting here, if they are not your actual column names); I replaced them with USR and RL.

I assumed there are only four possibilities, with the following rules. If my assumption is wrong, you should be able to adapt the query to your actual possibilities and rules; you should be able to see easily how each possibility and rules are reflected in the query.

If there are only emp at a place, they all have medBoss for boss.

If there are only emp and smallBoss, then emp have smallBoss as the boss, and smallBoss has bigBoss.

If there are emp, smallBoss and medBoss, then smallBoss is the boss of emp, medBoss is the boss of smallBoss and bigBoss is the boss of medBoss.

If there are emp, smallBoss, medBoss and bigBoss, then the rules are like in the last paragraph, and bigBoss has no boss (null) - that is the most common way to represent that in a hierarchical table.

with
     test_data ( id, place, usr, rl ) as (
       select 1, 'a', 'u1', 'emp'       from dual union all       
       select 2, 'a', 'u2', 'emp'       from dual union all        
       select 3, 'a', 'u3', 'smallBoss' from dual union all   
       select 4, 'b', 'u4', 'emp'       from dual union all 
       select 5, 'b', 'u5', 'emp'       from dual union all 
       select 6, 'b', 'u6', 'smallBoss' from dual union all 
       select 7, 'b', 'u7', 'medBoss'   from dual
     )
-- End of test data (not part of the query). The SQL query begins BELOW THIS LINE.
select id, place, usr, rl,
       case max(rl_code) over (partition by place)
                         when 0 then 'medBoss'
                         when 1 then case rl_code when 0 then 'smallBoss'
                                                  when 1 then 'bigBoss'    end
                         when 2 then case rl_code when 0 then 'smallBoss'
                                                  when 1 then 'medBoss'
                                                  when 2 then 'bigBoss'    end
                         when 3 then case rl_code when 0 then 'smallBoss'
                                                  when 1 then 'medBoss'
                                                  when 2 then 'bigBoss'    end
                         end as boss
from   (                                            
         select   id, place, usr, rl,
                  case rl when 'emp'       then 0
                          when 'smallBoss' then 1
                          when 'medBoss'   then 2
                          when 'bigBoss'   then 3 end as rl_code
         from     test_data
       )
order by place, id    -- if needed
;

Output:

ID PLACE USR RL        BOSS
-- ----- --- --------- ----------
 1 a     u1  emp       smallBoss
 2 a     u2  emp       smallBoss
 3 a     u3  smallBoss bigBoss
 4 b     u4  emp       smallBoss
 5 b     u5  emp       smallBoss
 6 b     u6  smallBoss medBoss
 7 b     u7  medBoss   bigBoss

7 rows selected.

Upvotes: 1

Related Questions