David,

SELE ...fields... ;
  FROM customers LEFT [OUTER] JOIN invoices;
    ON customers.customer_id = invoices.customer_id;
  WHERE invoices.order_dt BETWEEN....

It's OK to apply the filter to the invoices, because the magic is in the "LEFT JOIN" clause, which returns all customers. Note that the use of the word OUTER is optional, and does not affect behavior.

Note that records returned from the invoice table that don't match up will have .NULL. in all of their fields.

Hope this helps.

>This is a fairly generic question which has cropped up for me several times recently.
>
>How do I get an outer join to work if I need to have a filter on an inner table?
>
>e.g. The usual example - I have a table of customers and a table of invoices, I want the output of my query to list all customers regardless of whether or not they have any invoices together with the total owing from their invoices.
>Now so far this works fine, but here is the problem, say I want to restrict the invoices to those for the last month only. My query now only lists the customers which actually had an invoice in the last month. But what I want to still list all customers.
>
>As far as I can see putting any kind of WHERE condition on the inner table immediately turns the whole query into an inner join.
>
>I hope I am missing a trick here - if not it looks like I'm back to all those UNION queries we had to do before.
>
>Please help if you can.
>
>David