MSDN Magazine - September 2008 - (Page 61)

on delete cascade on update no action; alter table dbo.billPartList add constraint fkBillPartList_Bill foreign key(billID) references dbo.bill(billID) on delete cascade on update no action; join dbo.billPartList bpl on c.billID = bpl.billID left join dbo.partList pl on bpl.partListID= pl.partListID join dbo.part p on pl.partID = p.partID You can now insert some data into the tables for testing. Given the amount of data involved, I will not show the code for that here. If you download the sample code for this article, simply open and run the script named 01_data.sql to generate Figure 4 Querying a Hierarchy of Bills this sample data. The next query inverts the previous que ries. Instead of traversing the data topdown from the topmost parent bill, you can see that it starts with a part, queries for the bill it belongs to, and then works its way up the hierarchy to find all the bills that use the part: with c as ( select b.descr,b.billID,b.parentBillID,0 as lvl from dbo.partList pl left join dbo.billPartList bpl on pl.partListID = bpl.partListID left join dbo.bill b on bpl.billID = b.billID where pl.partID = 19 union all select b.descr,b.billID,b.parentBillID,lvl+1 from dbo.bill b join c on c.parentBillID = b.billID) select * from c; Queries to Validate the Design Writing and executing some simple que ries will help test and validate that the design works as expected. My first query uses the new common table expressions syntax to produce a hierarchical list of bills: with c as ( select billID,parentBillID,descr,0 as [level] from dbo.bill b where b.parentBillID is null union all select b.billID,b.parentBillID, b.descr,[level] + 1 from dbo.bill b join c on b.parentBillID = c.billID) select descr,[level],billID,parentBillID as bill from c Figure 5 Listing the Nested BOMs Visualizing how the bills interrelate using the rowwise listing can prove diffi cult. In such cases, it makes sense to orga nize the bills into paths, as shown below. By doing so, you can easily visualize the nesting of bills: In the returned data shown in Figure 4, there is a single root node (where the parentBillID is null). If you had more than one such bill, you would need to change the query to select that parent by the bill’s ID. (For more on common table expressions, see the October 2007 Data Points column at msdn.microsoft.com/magazine/cc163346.) The next query builds on the first by listing the each of the parts used for the system bill returned by the first query. This query ex ercises all of the relationships between the tables: with c as ( select billID,parentBillID,descr,0 as [level] from dbo.bill b where b.parentBillID is null union all select b.billID,b.parentBillID,b.descr,[level] + 1 from dbo.bill b join c on b.parentBillID = c.billID) select p.partID,p.descr from c join dbo.billPartList bpl on c.billID = bpl.billID left join dbo.partList pl on bpl.partListID = pl.partListID join dbo.part p on pl.partID = p.partID group by p.partID,p.descr; with c as ( select '/'+cast(billID as varchar(49)) as path,BillID from dbo.bill b where b.parentBillID is null union all select cast(c.path+'/'+CAST(b.billID as varchar(4)) as varchar(50)), b.billID from dbo.bill b join c on b.parentBillID = c.billID) select c.path+'/',b.descr from c join dbo.bill b on c.billID = b.billID order by 1; You can see the resulting nest in Figure 5. Figure 6 Adding a New Part and Bill begin transaction declare @mp int; declare @mpl int; declare @mb int; select @mp = MAX(partID)+10 from dbo.part; insert into dbo.part(partID,descr,cost) output inserted.* values (@mp,'4GB USB 2.0 Memory Stick',20.0); select @mpl = MAX(partListID)+10 from dbo.partList; insert into dbo.partList(partListID,partID,quantity) output inserted.* values (@mpl,@mp,1); select @mb = MAX(billID)+10 from dbo.bill; insert into dbo.bill(billID,parentBillID,descr) output inserted.* values (@mb,110,'Summer Bonus Package') insert into dbo.billPartList(billID,partListID) output inserted.* values (@mb,@mpl); commit; go select * from dbo.bill where parentBillID = 110; select * from dbo.part; go The next query then computes the manufacturer’s suggested retail price (MSRP) for the system by summing the cost of the required system parts and multiplying by 2: with c as ( select billID,parentBillID,descr,0 as lvl from dbo.bill b where b.parentBillID is null union all select b.billID,b.parentBillID,b.descr,lvl + 1 from dbo.bill b join c on b.parentBillID = c.billID) select SUM(p.cost*pl.quantity) * 2.0 from c msdnmagazine.com September 2008 61 http://msdn.microsoft.com/magazine/cc163346 http://msdnmagazine.com

Table of Contents Feed for the Digital Edition of MSDN Magazine - September 2008

MSDN Magazine - September 2008 Contents Toolbox CLR Inside Out Data Points Advanced Basics Office Space Cutting Edge Hierarchy ID New Features for Microsoft SQL Server 2008 Prism Data Services Advanced WPF Test Run Security Briefs Foundations { End Bracket }

MSDN Magazine - September 2008

For optimal viewing of this digital publication, please enable JavaScript and then refresh the page. If you would like to try to load the digital publication without using Flash Player detection, please click here.