Sorry, two errors, should be ...
...) OR m.repyear IS NULL
GROUP BY c.year
PB

-----

Chris Fonnesbeck wrote:
On 2/17/06, Peter Brawley <peter.brawley@earthlink.net> wrote:
  
Chris,

Your WHERE clause is weeding out the NULL joined entries. Try something
like...

select
 m.repyear as repyear,
count(*) as count
from years y
left join mortality m on y.year=m.repyear
where (
m.region like 'Southwest'
and m.repyear>1985
and m.dthcode!=4
and (m.cause like '%red tide%' or m.remarks like '%red tide%')

group by m.repyear;
    

Thanks for the recommendation; unfortunately, it does not work:

mysql> select
    ->  m.repyear as repyear,
    -> count(*) as count
    -> from years y
    -> left join mortality m on y.year=m.repyear
    -> where (
    -> m.region like 'Southwest'
    -> and m.repyear>1985
    -> and m.dthcode!=4
    -> and (m.cause like '%red tide%' or m.remarks like '%red tide%')
    -> ) OR m.repyear IS NULL
    -> group by m.repyear;
+---------+-------+
| repyear | count |
+---------+-------+
|    1994 |     1 |
|    1996 |   145 |
|    1997 |    15 |
|    1999 |    12 |
|    2000 |    14 |
|    2001 |    16 |
|    2002 |    36 |
|    2003 |    91 |
|    2004 |     5 |
|    2005 |    52 |
+---------+-------+
10 rows in set (52.92 sec)

Thanks,
C.

--
Chris Fonnesbeck + Atlanta, GA + http://trichech.us