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