Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

VB.Net - SQL Query and Design Question

Avatar of Jimbo99999
Jimbo99999Flag for United States of America asked on
Visual Basic.NET
4 Comments1 Solution138 ViewsLast Modified:
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,
jimbo99999