Section 7.10. Joining Arrays with Master Tables

   

7.10 Joining Arrays with Master Tables

7.10.1 Problem

You want to join a non-normalized array-like data table with a normalized master record. The results should still be non-normalized, but instead of showing coded values from the detail table, you want them to show the corresponding values from the master table.

Taking both the StudentThesis table and the Professors table, you want to join them to replace the member codes with the corresponding professor names. You want to do it so that the structure of the result corresponds to the structure of the StudentThesis table ” you want the resulting rows to have one student ID, three professor names , and one grade.

7.10.2 Solution

The following SELECT statement reproduces the structure from the non-normalized table (StudentThesis), but replaces the professors ID numbers with their corresponding professor names. Each line of the report will list a student ID together with the names of the three dissertation committee members for that student.

 SELECT t.StudentId,    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 StudentThesis t, Professors p GROUP BY t.StudentId 

The results from executing this query will look like these:

 StudentId  Member1         Member2          Member3 ---------- --------------- ---------------- ---------------- 1          Baird, Frances  Anderson, Terry  Newton, Isaac 2          Newton, Isaac   Anderson, Terry  Smith, John 3          Newton, Isaac   Einstein, Albert Base, Samuel 4          Anderson, Terry Smith, John      Einstein, Albert 

7.10.3 Discussion

The query shown in this recipe is driven by the StudentThesis table, and it uses aggregation combined with conditional logic to link each member Id number with its corresponding master record in the Professors table. You may well wonder about the need for aggregation. To explain that, let's start by looking at the following, simpler query:

 SELECT t.StudentId, p1.Name, p2.Name, P3.Name, t.Grade FROM     StudentThesis t JOIN Professors p1 ON t.Member1 = p1.Id    JOIN Professors p2 ON t.Member2 = p2.Id    JOIN Professors p3 ON t.Member3 = p3.Id 

This query also returns professor names in place of their Id numbers, but it does so by joining one instance of the Professors table for each member Id column in the StudentThesis table. The large number of table joins, three in this case, will reduce the efficiency of the query. To understand how aggregation can help us, let's take a look at the Cartesian product that you get when you join the StudentThesis and Professor tables together. Here's an excerpt that shows the Cartesian product for one student record:

 StudentId Member1 Member2 Member3 Grade Id  Name                  --------- ------- ------- ------- ----- --- --------------------  1         234     322     456     A     123 Smith, John          1         234     322     456     A     456 Newton, Isaac        1         234     322     456     A     455 Einstein, Albert     1         234     322     456     A     344 Base, Samuel         1         234     322     456     A     322 Anderson, Terry      1         234     322     456     A     234 Baird, Frances 

Because you have all possible combinations of all rows, you are ensured that for any given member Id column from the StudentThesis table, you will have at least one row with the corresponding professor's name from the Professors table. The following CASE expression, which happens to be for the member1 column, takes advantage of that fact to return the appropriate professor name when the Id in the member1 column matches the Id column of the Professors table:

 CASE WHEN t.Member1=p.Id       THEN p.Name       ELSE NULL END 

When there's a match between the two Id values, the professor name is returned by the CASE expression. Otherwise, when there's no match, a NULL is returned. The result is that the Cartesian product now looks like this:

 studentid  member1   member2   member3   grade  id   name              ---------- --------- --------- --------- ------ ---- ----------------- 1          NULL      NULL      NULL      A      123  Smith, John       1          NULL      NULL      Newto...  A      456  Newton, Isaac     1          NULL      NULL      NULL      A      455  Einstein, Albert  1          NULL      NULL      NULL      A      344  Base, Samuel      1          NULL      Ander...  NULL      A      322  Anderson, Terry   1          Baird...  NULL      NULL      A      234  Baird, Frances 

This is fine, but we really want one row to be returned for each student, not six rows as shown here. We can use aggregation to collapse these six rows into one. The recipe query uses GROUP BY t.StudentId to accomplish that. The name and id columns from the professor table aren't needed, so they aren't included in the select list. The MAX function is used to extract the one relevant value from each member column. Each column has only one non-NULL value. Since aggregate functions ignore NULLs, that one value is the one returned by MAX.

While the query using aggregation is more complex than the simple query shown earlier, it's more efficient because only one table-join is needed instead of three. A further advantage is that the number of joins does not increase as the number of member columns increases .

   


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