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@stripped> 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
>
>
>
Attachment: [text/html]
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.11/264 - Release Date: 2/17/2006
