To join with missing records, use an outer join. For example, if some orders_header may have no orders_detail:

select Orders_header.OrderDate, Orders_detail.Product;
  from Orders_header left join Orders_detail on Orders_header.order = Orders_Detail.order;
  ...

If the second table has no corresponding record, the corresponding fields (from the second table) will be NULL. You can convert this to another value (like zero) with the nvl() function.

>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!