Complicated 4 Table Query.

[Guest]

Hi guys, bit of a complicated one here and I'll try my best to explain the
problem.

I have 4 tables.

Orders
=======
OrderNumber
Customer
Contact
OrderDate
CustomersOrderNumber

Order_Details
=========
OrderItemID
ItemNumber
ItemType
Description
Quantity
Price

Deliveries
=======
DeliveryNoteID
OrderNumber
Date
DespatchMethod

Delivery_Details
===========
DeliveryDetailID
ItemNumber
QtyDelivered
ItemComplete

All four are related to each other. The idea is that you have an order
(Orders) which has various items (Order_Details), against each order you then
get a delivery (Deliveries) which also has various items (Delivery_details) -
which relate to the order's items.

I basically want to run a query that will list all orders and their items
and also any deliveries and delivered items against each order. Which is fine
apart from one problem. For orders where there are no deliveries nothing will
be shown, but I want the results to show 0 instead of showing nothing.

So can anyone tell me what SQL I need to write to achieve this?

 

[JethroUK©]

for existing records you need to run an update query (replace nulls with 0)
and for future records you need to set default field values