Maverick Riz Maverick Riz - 2 months ago 15
SQL Question

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.

Answer

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.)