I have four tables:

Orders_header, Orders_detail, Inovoice_header, Invoice_detail.

Making a long sad story short - some of the invoices don't have all the item details because the item wasnt shipped, but of course NOW someone needs a report showing the item anyway - with a quantity of zero. To make matters worse - it all needs to be done in one SQL Statement....which is where I'm stuck. Anoying because I've actually done this before - just can't remember how the heck I did it..grrrr

So...tables look like this:

Orders_header
-------------------
MasterNum N(10)
OrderNum N(10)

Orders_detail
------------------
OrderNum N(10)
ItemID C(3)
LineItemNum N(3)
QuantityOrdered N(3)

Invoice_header
--------------------
MasterNum N(10)
InvoiceNum N(10)

Invoice_Detail
--------------------
InvoiceNum N(10)
ItemID C(3)
LineItemNum N(3)
QuantityShipped N(3)


....and I want the output too look something like this:

OrderNum InvoiceNum ItemID LineItemNo QuantityOrdered QuantityShipped
1 1 A 1 10 10
1 1 B 2 15 12
2 2 C 1 8 8
2 2 D 2 5 0

...if that makes any sense at all. So you can have multiple inovices per order, and in some cases there will be fewer line-items in the invoice_detail compared to the order_detail, because only shipped items get written to the invoice_detail table..

Seems to me, that with a few left-outer joins you should be able to do this in one SQL statement - but that stupid ZERO and missing record from th invoice_detail table screws me up - and for the life of me I cant recall how I got around this before...

Anyone have any suggested SQL statements here???

Thanks!