Hi, folks. Suppose I need to create invoices for a set of customers, each invoice consisting of a block of customer information followed by a variable number of detail lines. Standard stuff, but I'm used to reading records instead of executing queries, so I'm a little confused here.

Do I use one query to gather the desired set of customers, and then loop through the set of customers, issuing a query for each customer's detail lines? Or do I use one query that gathers the desired set of customers AND their detail lines all at once?

In the first case, I see that many more queries are involved, while in the second case, the customer information is needlessly repeated in each row. However, the amount of data involved is not large enough to make a performance difference either way, so I'm asking more if there's a standard way.

A single query to gather all the necessary data is preferred over multiple queries when not utilizing some type of ORM automation, caching layer. When using an ORM, with cache support it would probably be more efficient, in the long run to issue single queries for each separate entity. To gain the advantage of an ORM, it is many times necessary to break up what can be done in a single queries into multiple requests. However, that doesn't seem to be the case here, so stick to a single query.

I just wasn't clear on the preferred approach to get the data for reporting purposes. Although using a single query gives you a table with lots of redundant data, I guess that's better than hammering the server with queries.

you might not think this technique would apply in this instance since the columns between the customers and the order details are going to be quite disiimilar... still, there's no problem doing it, really, if you can live with multiple NULL columns in the two different row types from the UNION

r937 -- Thanks for the UNION explanation. I may have to use that since I'm not getting a single query to work.

The sticking point are the aggregate functions. The balance for each invoice is not stored but rather calculated from the detail records when needed. For a single customer, the SUM() function works just fine, but if the query includes the invoice detail of several customers (as it would during a batch print of invoices), the SUM() function gives me the sum of all transactions. If I add a GROUP BY clause, the SUM() then correctly gives me the balance for each customer, but it also returns only a one row summary per customer.