We've examined how to retrieve the
Professor tables using
JOINs. While there is nothing wrong with using
JOINs, some folks find the syntax tricky. Also, the data received needs some formatting. In Part 1, when we listed the professors and the classes they taught, a professor's name was listed as many times as the number of classes he or she taught! It would be nice to have the output formatted like:
Dr. Mitchell Formal Languages & Automata Theory Study of Film Software EngineeringWhile this can be done using a little ASP logic, it is a bit nasty.
Data shaping is a new feature of ADO 2.0. It allows us to grab data from a database, and puts the related data in a recordset. So, in our example, we could bring back a simple recordset of professors, which could contain their
PhoneNumber, and classes taught. The classes taught field would be a recordset itself, containing all of the class information! This may seem a bit mind blowing, since we are retrieving recordets containing recordsets!
This is why data shaping is so cool, though!
We have to use a special syntax for data shaping.
parent SQL statement and the
child SQL statement are both just simple SQL statements. If we wanted to obtain a list of professors and classes they taught, the SQL statement to retrieve the list of professors would be the
parent SQL statement, while the SQL statement to retrieve the list of classes would be the
child SQL statement. The two statements would be related on
ProfessorID. Therefore, the syntax for our data shape would look like:
What if we wanted to obtain a listing of the classes and the professors that taught them? Well, we'd simply reverse the parent and child SQL statements. The syntax would change to:
It is important that the column you are relating appear both in the parent and child SQL statements. If you leave out the relating column from either SQL statement, you will get an error.
Now that we've looked at the syntax for data shaping, we still have to examine how to obtain a data shape through ADO. We'll focus on this in Part 3 of this article!
(Read Part 1)