CODEWITHSUNDEEP
sqloracle-databaseoracle11gpivotunion-all
Maverick Riz
Maverick Riz

Reputation: 2065

How to merge results of an Union All in Oracle

Consider i have 2 queries with the below result set...First result set is for students, so all the columns pertaining to instructors are null.. The second result set is pertaining to instructors and column related to students are null and both of them share few common columns.. Student:

 uid f_name m_name l_name class school Section Dept. Branch Title
 1   abc    c     dey   2     NYU    1   null    null     null
 2   cde    d     rey   3     CU     2   null    null     null
 3   xyz    r     mey   4     LSU    3   null    null     null

Teacher:

uid f_name m_name l_name class school Section Dept. Branch Title
4    wss    c     tey  null    null     null   Science  Biology  Asso.Prof
2    cde    d     rey  null    null     null   Arts     Music    Asso.Prof
5    rrr    r     jey  null    null     null   Science  Chemistry Prof

If you look in the above result set, UID 2 is common in both the result set, that basically means a professor can also be a student at the same time... Now i want to join/merge these two queries into a common resultset say 'Users' who are basically the teachers and students.

The result set of 'Users' should be unique with respect to UID. If i use union all, there will be duplicates on UID 2. I need a query which can merge the columns for a single row... The result set should be:

1   abc    c     dey   2     NYU    1   null    null     null
2   cde    d     rey   3     CU     2   Arts    Music     Asso.Prof
3   xyz    r     mey   4     LSU    3   null    null     null
4   wss    c     tey  null    null     null   Science  Biology  Asso.Prof
5   rrr    r     jey  null    null     null   Science  Chemistry Prof

Note 2 above, it has both student and professor details in one row...

How can i achieve this in Oracle ? Appreciate your help.

Upvotes: 0

Views: 2847

Answers (2)

user5683823
user5683823

Reputation:

As has been mentioned in Comments, this is a poor design (or perhaps a poor solution using a good design). If the base tables are "person" (containing only information that is of the same kind for students and instructors, such as UID, name, date of birth, email etc.), "student" (with UID as foreign key, and showing only characteristics specific to students) and "teacher" (same for teachers or instructors), then the design is fine, and your desired final output can be obtained directly from these base tables, not from the results of other queries you have written. jva shows something along these lines in his/her answer.

If you really have to put up with it, the way to use your existing queries is to use union all - but then you must group by uid, and for each column you must select max(...) - for example max(f_name) and max(school). Use column aliases in the SELECT clause, for example select .... , max(school) as school, ...

Or, slightly more efficiently (but with some risks), group by id, f_name, m_name, l_name and select max(...) only for the remaining columns. The risk is that in one place the m_name is N and in the other it is null; that is, your data is internally inconsistent. (If it all comes from one base table, "person", then this risk shouldn't exist.)

Upvotes: 1

jva
jva

Reputation: 2807

This would be the general approach:

SELECT persons.id
      ,nvl(<some_student_field>, <same_teacher_field) as some_common_field
      ,... 
FROM persons 
     LEFT OUTER JOIN students on (person.id = students.person_id)
     LEFT OUTER JOIN teachers on (person.id = teachers.person_id)
WHERE <mandatory_field_for_students> IS NOT NULL
   OR <mandatory_field_for_teachers> IS NOT NULL

Upvotes: 1

Related Questions