join or union two tables?

Hi,

I have an Orders Table and a Freeshipping Table.

ORDERS
======
CustomerId OrderDate Quantity
========== ========= ========
1000 01/01/2003 5
1000 01/04/2003 9
1000 01/08/2003 14
2000 01/01/2003 4
1000 06/03/2003 9
4000 05/02/2003 4
FREESHIPPING
=============
CustomerID FreeDate
========== ========
1000 01/01/2003
1000 01/03/2003

How can I write a query that will return the following result to show the order details for customerid 1000
for a date range between 01/01/2003 and 01/08/2003

OrderDate Quantity FreeShipping
========= ======== ============
01/01/2003 5 Y
01/03/2003 0 Y
01/04/2003 9 N
01/08/2003 14 N

Note that even if an order was not placed and the date was a freeshipping date, it is still displayed in the resultset

Thanks in advance,
-ronThis query can probably be optimized some more but gives the result you're after:

Select A.OrderDate, IsNull(B.Quantity,0) as Quantity, CASE WHEN IsNull(FreeShipping.FreeDate,'1/1/1900') = '1/1/1900' THEN 'N' ELSE 'Y' END as FreeShipping
FROM (Select OrderDate from Orders WHERE OrderDate BETWEEN '1/1/2003' AND '1/8/2003' Union Select FreeDate from FreeShipping WHERE FreeDate BETWEEN '1/1/2003' AND '1/8/2003') A
LEFT OUTER JOIN (Select Orders.OrderDate, Sum(Orders.Quantity) as Quantity From Orders WHERE Orders.CustomerID=1000 Group By OrderDate) B ON A.OrderDate = B.OrderDate
LEFT OUTER JOIN FreeShipping on (A.OrderDate = FreeShipping.FreeDate)

|||Thank you very much.

-ron