List:General Discussion« Previous MessageNext Message »
From:Randy Chrismon Date:December 4 2003 5:28pm
Subject:How to query
View as plain text  
I'm stumped...
I have a pretty standard invoice system:

create table invoice(
  InvoiceNum varhcar(16),
  ClientInfo ....
)
create table line_items(
  InvoiceNum varchar(16),
  ProductCode varchar(10),
  Description varchar(25),
  Quantity .....
)

Three is, of course, a one-to-many relation between invoice and
line_items. 

Now, I need to generate reports for a specific client. In the end,
for
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
widgets, together.
3. Which invoices for the purchase of green doodads, only.
4. Which for green doodads along with yellow widgets, together.
etc.

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
by
the 10 or so products I'm interested in. 
MySQL is 4.0.12. 
Thanks. 
Randy
Thread
How to queryRandy Chrismon4 Dec
  • Re: How to queryRandy Chrismon5 Dec