I have a pretty standard invoice system:
create table invoice(
create table line_items(
Three is, of course, a one-to-many relation between invoice and
Now, I need to generate reports for a specific client. In the end,
a given month, I have to tell the client
1. Which invoices were for the purchase of blue widgets, only.
2. Which invoices were for the purchase of blue widgets and yellow
3. Which invoices for the purchase of green doodads, only.
4. Which for green doodads along with yellow widgets, together.
Out of 1,000 product codes, I'm interested in 10 or so. It's easy
enough to write any one of the queries but I can't figure out how to
group/order in such a way as to do it all in one query and to order
the 10 or so products I'm interested in.
MySQL is 4.0.12.