ADO.NET Multiple Table Relationships

[tate]

Well, I have spent uncountless hours searching for examples on how to code a web project that utilizes relationships between multiple tables without any luck. I found numerous examples of how to iterate through a single one-to-many realtionship but that was about as adventurous as it got.

 

My challenge (see atttached relationships) is to generate a datagrid with the following output;

 

Customer #

Last Name

Order #

Order Date

Item Desc

Order Qty

Order Date

Unit Price

Ext Price

 

Can anyone point me to a good reference or provide some sage advice on how to navigate through the relationships to produce the required output?

 

Thanks in advance for your help.

Tate

[wessamzeidan]

what you need is an sql query that joins the three tables, got nothing to do with ado.net, read more about sql queries......

[Robby]

This can be solved with a single Select statement, the question is how do you plan on displaying the data to the user, will you use a single datagrid or parent/child grids or something else.

[Robby]

Here's a Select that will give you the required columns...

 

SELECT C.CustNum, C.LastNAme,

O.OrderNum, O.OrderDate,

I.Description, L.LIQuantity,

I.Cost, I.Price

FROM (Customer AS C INNER JOIN Orders AS O ON C.CustNum = O.OrdCustNum)

INNER JOIN (Item AS I INNER JOIN OrderLineItem AS L ON I.IemNum = L.LIItemNum)

ON O.OrderNum = L.LIOrdNum

[tate]

Thanks I see how the SQL statement is utilized now. My hope is to utilize a datagrid to display the results. So I can use the SELECT statement when creating the data adapter. Now. most of the time when I fill a dataset a specific table is given as part of the syntax. How do you accomplish this when multiple tables are involved or should a dataview be created?