Help with One to Many Data Set

[linuxjr]

I'm looking around for examples or suggestions of how to go about my problem.

I have three tables (work, engineers, parts) that are joined by a column wo_number.

When I do my query in sql:

select work.wo_number, engineer.name from work join engineers on work.wo_number = engineer.wo_number

It returns the correct layout for the number of engineers that worked on this particular call example
Work Number Name
----------------
1 Joe
1 Jane

But my problem is when I add the parts that was used on the call

select work.wo_number, engineer.name, parts.part_number from work join engineers on work.wo_number = engineer.wo_number join parts on work.wo_number = parts.wo_number

it returns the correct number of parts but in this format:
Work Number Name Part
----------------------
1 Joe Part1
1 Jane Part1
1 Joe Part2
1 Jane Part2
1 Joe Part3
1 Jane Part3
etc.

As you can see this is very ugly stepping through to see how many distinct engineers and distinct parts are used on the call.

I checked the sql forums and they all pretty much said to write an application to produce an output such as

Work Number Engineer Part
-----------------------------
1 Joe Part 1 Part 2 Part 3 Part 4
2 Jane Part 5 Part 6 Part 7 Part 8

I'm asking how do I go about to writing a program that will help me produce an output similiar to this. Any tips or suggestions be greatly appreciated.

 

[CrashDome]

With a dataset, you can do this very simply.

A dataset mimics a database in many ways in that there are tables and relations. You fill each table with all the data for the work order number and then merely do a select command on each table.

However, My guess is that you are much more in need of a report writer like Crystal Reports or the MS SQL Report Service if you are just looking to output the data in some readable format. If I were doing this in Crystal Reports I'd do a query on the WO table with the Names and then do a sub-report for each name and list the parts. Would be much easier than writing a full-blown program for this one purpose.

If you are DO want a full-blown program that will manipulate the data... well, thats another story.

 

[linuxjr]

CrashDome,
Thank you for replying to my question. I am basically trying to produce a spreadsheet with the data in a certain format like above in my previous post. I already have reports that I can format correctly but I'm trying to write for one of our customers a way to get this information out in an Excel format.

So that is why I was asking how to go about using datasets/datatables etc.

 

[CrashDome]

Keep in mind alot of report writers offer exporting to excel format (like Crystal), but if you really need to write a seperate program to further control the data I would suggest determining what method is best for your skill level. I could write a suggestion which would do what you ask but it would take me longer than I care to spend on answering this.

My advice would be to research the DataTable.Select() method. Put all your datatables in one dataset and perform some select methods on them so that you get the data you need, like:

Note: Assumes you populate the dataset with specific rows from each table that are filtered by one specific order like using "WHERE OrderID = @OrderID" in each Stored Procedure and NOT with all orders and all workers, etc...

   //Assume "OrderRow" is a single DataRow from your Order DataTable

   //Get All Workers for Order (Selects All Rows in DataTable)
   DataRow[] WorkerRows = MyDataSet.MyWorkerTable.Select();

   //For Every Worker
   foreach (DataRow WorkerRow in WorkerRows)
   {
      //Get All Parts (Select only rows for that worker)
      DataRow[] PartRows = MyDataSet.MyPartTable.Select("WorkerID = "+(int)WorkerRow["WorkerID"].ToString());

      //Write All The Data In The Format I Want - Here
   }

This is somewhat crude but helps better explain what you can achieve.

There is other methods which I would choose over this but require alot more explanation and depends a lot on how the data is structured and how it is ultimately used.

 

[CrashDome]

My post got cropped in the code section above...

After "//Write all the data..."

it should also have said:

Console.Write(OrderRow["OrderNumber"].ToString()+","+
      WorkerRow["Name"].ToString());
foreach (DataRow PartRow in PartRows)
{
   Console.Write(","+PartRow["PartNumber"].ToString());
}

//End of Line
Console.WriteLine();