Link to home
Start Free TrialLog in
Avatar of branuda
branuda

asked on

Need a SQL query that creates a header row and one or more detail rows.

I have a SQL database that has a table for invoice header information and another for invoice detail information linked on invoice number.  There is a 1:many relationship between header and detail.  I need to write a query that dumps the header record in the first row followed by one or more rows of the applicable detail.

Thanks in advance for any help.

Example of desired output:

Invoice 1, Date, Amount
Customer A, Purchase Order A, Detail Amount A
Customer B, Purchase Order B, Detail Amount B
Invoice 2, Date, Amount
Customer C, Purchase Order F, Detail Amount F

and so on for every invoice/detail set
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Make query to produce header information and another to show detail rows and post it here so we can work with.
First query possibly will have TOP 1 to limit to 1 row for header then union it to the detail data. Detail query will use top 3 to limit its row to 3.
Avatar of branuda
branuda

ASKER

Thanks Mike.   There is no limit to the number of detail rows.  Will it work with no limit on the detail data?
re:> There is no limit to the number of detail rows

We will use TOP 3 in its query. This way, it will limit it to 3 rows max.
Avatar of branuda

ASKER

Hi:
I need a way to associate the header with its associated detail.  This doesn't tie the two together.  Also and required by UNION, the number and type of fields in the header and detail are not the same.
Thanks!

select invoice, date, amount from invoiceHeader
union
select customer,purchaseorder, amount from InvoiceDetail
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of branuda

ASKER

Pawan:
Thank you for taking the time to help me with this - it works BEAUTIFULLY!
Carol
Glad to help!