Sorry, two errors, should be ...
...) OR m.repyear IS NULL
GROUP BY c.year
PB
-----
Chris Fonnesbeck wrote:
On 2/17/06, Peter Brawley <[EMAIL PROTECTED]> 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
|
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
