VB.Net - SQL Query and Design Question

Good Day Experts!

I am working on a project that I need a some assistance/direction if you can help.  

I have a staging table that contains Invoice Numbers and Order Numbers.  There are multiple lines with the same Invoice Number as multiple Order Numbers can be invoiced on the same Invoice Number.   It looks like the following:

Invoice     Order
1234         A
1234         B
1234         C
5678         A
5678         B
5678         C
4321         A
4321         B
4321         C

I have to now go get the "detail" for each Order on the Invoice and output a text file for each Invoice.  So, the above data would produce 3 separate files(1 for each Invoice).  

I could do a select * on the table and then when the InvoiceNumber changes start a new file.  However, instead I would like to have the InvoiceNumber as the "OuteLoop" trigger to create the file.  But the trouble I am having is figuring how to get those unique InvoiceNumbers while getting a list of the OrderNumbers related to each InvoiceNumber.  

Do you think this is possible all in one query? Or do I need to do Select distinct InvoiceNumbers for the outer loop then once inside the loop query the table again for the related OrderNumbers so I can go get the detail information?

Thanks for the suggestions and insight,
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you apply a sorting:
select ...
  from yourtable
order by Invoice, 'Order

and you loop on that one;
each time the invoice changes, you close the previous file (if any), and open a new one
after the loop, don't forget to close the open file (if any)

or, indeed you loop on the invoice:
select invoice from yourtable group by invoice

and on each of those line, get all the orders (and their details)
select order ... from yourtable where invoice = x

you may also (which is the eventually recommended method) load the data into a dataset, and make relations, then you can fetch the data in 1 go, and still loop properly as you requested
Guy Hengel [angelIII / a3]Billing EngineerCommented:
see here to start with: http://msdn.microsoft.com/en-us/library/system.data.dataset.relations%28v=vs.110%29.aspx
you fetch the full listing (data table1)
you fetch the distinct invoice list from the data table 1 into data table 2
   => http://msdn.microsoft.com/en-us/library/wec2b2e6%28v=vs.110%29.aspx

and you have all you need
Jimbo99999Author Commented:
Excellent idea...I will try it out today.  Hopefully there won't be too many User fires to tend to.  

Jimbo99999Author Commented:
Thanks for the suggestion...it is working now!

Have a good day,
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.