Ok, I have an example table with the following information and query.
First up is the data, with the question following at the end.

Here's the SQL Dump:
<http://pastie.org/private/o7zzajdpm6lzcbqrjolgg>

Or you can use the included a visual below:

### Purchases Table

```
|  id   |   brand   |    date     |

    1        b1       2000-01-01 
    2        b1       2000-01-03 
    3        b2       2000-01-04
    4        b3       2000-01-08
    5        b4       2000-01-14

```

### Owners Table

```
id | firstname | lastname | purchaseid | itemCoupon | itemReturned | Accessories
 1     Jane        Doe          1           yes           no               4
 2     Jane        Doe          2           yes           no               2 
 3     Jane        Doe          3           no            no               1
 4     Jane        Doe          4           no            no               3
 5     Jane        Doe          5           no            yes              6

```

### The Query

```
SELECT brand, COALESCE( SUM( inTime.Accessories ) , 0 ) AS acessory_sum
FROM purchases
INNER JOIN owners AS person ON person.purchaseid = purchases.id
AND person.firstname =  'Jane'
AND person.lastname =  'Doe'
LEFT JOIN owners AS inTime ON person.id = inTime.id
AND purchases.date
BETWEEN DATE(  '2000-01-01' ) 
AND DATE(  '2000-01-05' ) 
GROUP BY purchases.brand

```

This gives the following expected result:

```
| brand | accessory_sum
   b1          6
   b2          1
   b3          0
   b4          0

```

### The question

Now, I would like to add to the query:

```
WHERE itemCoupon = 'yes' OR itemReturned = 'yes'

```

But this overrides the last join and when I do the same search above I get:

```
| brand | accessory_sum
   b1          6
   b2          1

```

Similarly I still want it to return `No results found` for `2000-01-04`, `2000-01-08` using `WHERE itemCoupon = 'yes' OR itemReturned = 'yes'`. Removing the `WHERE` gives me zeros for all brands if I try to do it another way.

Basically I want to keep the way the `WHERE` behaves but also keep the format that I described in the first example of the expected output.

As it is now, using `WHERE` destroys the way the last `LEFT JOIN` works with `COALESCE` which fills the remaining `brand` rows with zeros.