Introduction to Data Shaping

Designing database applications often requires that you pull information from multiple tables. For instance, to obtain a listing of customers and their orders, you must link the required tables with SQL join statements as demonstrated below:

SELECT Customers.CustomerID AS [Cust Id], Customers.CompanyName, Orders.OrderDate, [Order Details].OrderID, Products.ProductName, [Order Details].UnitPrice, [Order Details].Discount,

CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100 AS [Extended Price] FROM Products

INNER JOIN ((Customers

INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID ORDER BY Customers.CustomerID, Orders.OrderDate DESC;

Creating and Manipulating Databases with ADO

When you execute the above SQL statement, your output will match Figure 16-5.

JOINQUERY: Select Query

■ - Inj

3

C . J Id

Ournp-ify Nif t

O' jtr Date |Ordy-lD

Producl Name

L:i it P- CG I Discount I

Emended Price * j

.31351

Alfreds FuUerkiste

Ce-A?r-1S83

11011

Flstemysosl

821 50

o%!

WSO.OD =1

.A J" KJ

Allfhiji F Llltrkiüty

CÖ-Aar-ISSe

1101t;

Escargots de Bourgogne

SI 3 25

5%

J5CG.5J

ALF Kl

Alfreds FuUerkiste

1B-Mar-1SSG

1C%2

Grandma's Boysefiberr/ Spread

$25-00

5%

J3EO.ro

ALF Kl

Alfreds FuUerkiste

1 B-Mar-1998

10952

Rossle Sauerkraul

$45 ED

0%;

isi.zi

ALF Kl

Alfreds FuUerkiste

15-Jan-ISSe

IC635

Original Frankfurter grune SoCe

813.00

20%

ALFK]

Alfreds FuUerkiste

15-Jan-ISSÖ

1CE35

Radette Courdiwault

855-00

0%

5825.03

ALF Kl

Alfreds FuUerkiste

13-Qc1-1997;

10702

LakkaJikoon

516 UL

0%

_$27000

ALFK]

Alf "9 - = r -11 = rkiite

13-OC1-1997

10702

Aniseed Syrup

81DDD

a%|

iEO.m

ALF Kl

Alfreds FuUerkiste

03-OC1-1937

1C632

Vegie-apreari

843 90

0%

3878,00

ALF Kl

Alfreds FuUerkiste

2frAug-1987:

10643

Rflssle Sauerkraul

845 60

25%

5513.03

ALF Kl

Alfrade FuH&rkiste

^-¿JUJ-1S97

10643

Chartreuse verte

818.00

25%

};S3.93

ALF KJ

Alfreds FuUerkiste

üi-Aug-lty/

ILb'l J

Spegesild_

512 00

25%:

_$16.03

ANATR

Ana Trujillo Emparedsdos y heledos

Qd-Mar-1998i

10925

Mona^ella di Giovanni_

S3£ BC

a%i

1348.ro

AMATR

Ana Tnjjillo Emparedados y heledos

Od-Mar-1993j

1C933

Queso Cabrsles

821 00

0%|

t4? 01

zl

I Record: Ml 4 II U[i> I ►! M of 2155 <| | >\

Figure 16-5: When you use SQL join statements you get a flat recordset with a lot of duplicate information.

Figure 16-5: When you use SQL join statements you get a flat recordset with a lot of duplicate information.

When you output your data in a standard way by using the SQL Join syntax, you get a lot of duplicate information. You can eliminate this redundant information by using an advanced feature of the ADO known as a shaped (or hierarchical) recordset.

Data shaping allows you to create recordsets within recordsets with a single ADO object. This sort of hierarchical data arrangement is often seen as a parent-child relationship. The parent recordset contains the child recordset. A child recordset can contain another child recordset, a grandchild of the original recordset. A parent-child relationship can be placed in an easy-to-read tree structure. You will produce such a structure in Custom Project 16-4 later in this chapter. For now, let's focus on learning a couple of new concepts that will enable you to present your data in a format that's easy to view and navigate.

0 0

Post a comment