Section 7.11. Joining Arrays with Multiple Master Records

   

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 .

   


Transact-SQL Cookbook
Transact-SQL Cookbook
ISBN: 1565927567
EAN: 2147483647
Year: 2005
Pages: 152

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net