Re: Newbie query question...

[Michael Stassen]

Mike Johnson wrote:
From: John Croson [mailto:[EMAIL PROTECTED]


I have a simple query:

SELECT id,year,month,day,cat_id FROM events
LEFT JOIN calendar_cat ON
events.cat=calendar_cat.cat_id
WHERE year>=YEAR(CURDATE())
AND month>=MONTH(CURDATE())
AND day>=DAYOFMONTH(CURDATE())
AND cat_id='2' OR cat_id='5'
AND approved='1'
ORDER BY year,month,day ASC
Results:

+----+------+-------+-----+--------+
| id | year | month | day | cat_id |
+----+------+-------+-----+--------+
| 25 | 2003 |    12 |   7 |      5 |
| 44 | 2004 |     2 |  15 |      2 |
| 53 | 2004 |     3 |  28 |      5 |
| 45 | 2004 |     6 |   6 |      2 |
+----+------+-------+-----+--------+
Can anyone point out where I've screwed up the query??
I don't think it should be giving me the first entry.
It is a trivial problem, and I can filter the results
further with php, but I'd like to do it right, and am
trying to learn SQL.


You problem is in that OR.

Add a couple parentheses to get:

SELECT id,year,month,day,cat_id FROM events
LEFT JOIN calendar_cat ON
events.cat=calendar_cat.cat_id
WHERE year>=YEAR(CURDATE())
AND month>=MONTH(CURDATE())
AND day>=DAYOFMONTH(CURDATE())
AND (cat_id='2' OR cat_id='5')
----^------------------------^
AND approved='1'
ORDER BY year,month,day ASC
Previously, it was going through all the ANDs and then saying "oh,
/or/ I can grab rows with a catid of 5 (hence the inclusion of that
first row).
HTH!
An equivalent way to do this would be to use IN instead of OR, like this:

 SELECT id,year,month,day,cat_id FROM events
 LEFT JOIN calendar_cat ON events.cat=calendar_cat.cat_id
 WHERE year>=YEAR(CURDATE())
 AND month>=MONTH(CURDATE())
 AND day>=DAYOFMONTH(CURDATE())
 AND cat_id IN('2','5')
 AND approved='1'
 ORDER BY year,month,day ASC
By the way, are cat_id and approved really strings (CHAR or VARCHAR)? 
If they are integers (INT, etc.), you can leave out the quotes on the 
comparison values:

 SELECT id,year,month,day,cat_id FROM events
 LEFT JOIN calendar_cat ON events.cat=calendar_cat.cat_id
 WHERE year>=YEAR(CURDATE())
 AND month>=MONTH(CURDATE())
 AND day>=DAYOFMONTH(CURDATE())
 AND cat_id IN(2,5)
 AND approved=1
 ORDER BY year,month,day ASC
Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]