7.11 Joining Arrays with Multiple Master Records

7.11.1 Problem

You want to join a non-normalized table with multiple master records. With respect to our example, you want to write a query that returns results that match the structure of the StudentThesis table, but you want the member Id codes to be replaced by professor names and you want the student Id numbers to be replaced by student names as well. The problem is very similar to that in the previous recipe, but this time you are joining two tables instead of one.

7.11.2 Solution

Here is the query to create a report in which student IDs and member codes are replaced by their student and member names:

SELECT s.studentName,
   MAX(CASE WHEN t.Member1=p.id 
            THEN p.Name 
            ELSE NULL END) Member1,
   MAX(CASE WHEN t.Member2=p.id 
            THEN p.Name 
            ELSE NULL END) Member2,
   MAX(CASE WHEN t.Member3=p.id 
            THEN p.Name 
            ELSE NULL END) Member3 
FROM Professors p, 
   StudentThesis t JOIN Students s ON t.StudentId=s.StudentID
GROUP BY s.studentName

The results of executing this query will look like this:

StudentName        Member1         Member2          Member3              
------------------ --------------- ---------------- ---------------- 
Bohdan Khmelnytsky Newton, Isaac   Einstein, Albert Base, Samuel        
Ivan Mazepa        Anderson, Terry Smith, John      Einstein, Albert    
Jenny Gennick      Baird, Frances  Anderson, Terry  Newton, Isaac       
Teena Corlis       Newton, Isaac   Anderson, Terry  Base, Samuel

7.11.3 Discussion

The code is a clear example that joins on non-normalized data sets do not interfere with other operations. We can add additional joins to additional master records without any significant effect on the code or on the result. (Of course, adding a join always has an impact on performance.)

It's worth noting that aggregation was not needed on the s.StudentName column because that column replaced the StudentId column in the GROUP BY clause as the basis for grouping the rows together. The grouping is enough to ensure that we only see one occurrence of each student's name.

The solution shown in this recipe, and in the previous one, can be quite useful when manipulating legacy data sets to prepare quick, ad hoc reports.