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
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
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.
We will use TOP 3 in its query. This way, it will limit it to 3 rows max.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Pawan:
Thank you for taking the time to help me with this - it works BEAUTIFULLY!
Carol
Thank you for taking the time to help me with this - it works BEAUTIFULLY!
Carol
Glad to help!
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.