Multiple Queries with one to many relationship

[Dbless]

I have two tables Orders and Invoices. The relationship between the files is the order number. I need to select all the orders with order entry date between two dates, and select all the invoices with invoice date between two dates. There maybe more than one invoice per order, therefore I need to SUM the invoice amount and return it to the first query, so that I can have the information on a single line.

Any help would be appreciated.

 

[birgitd]

This is off the top of my head (don't have those tables handy in a database), but it should be the right idea.

I wasn't totally sure what you wanted here with the dates. This basic code should give you all orders between the dates specificed that also have invoices between the dates specified. It should give you the total amount of all invoices for that order.

Hope that helps,

Birgit


Select order_id, order_dt, invoice_dt, SUM(invoice_amt) from orders o, invoices i
where o.order_dt between date1 and date2 and i.inv_dt between idate1 and idate2 and o.order_num = i.order_num
group by order_id, order_dt, invoice_dt
sort by order_id