MySQL Forums
Forum List  »  Newbie

Query works in 4.1.9 but dies on 4.1.11 and 5.03
Posted by: Brian Turek
Date: April 08, 2005 07:29AM

If someone can take a look at this and see why this query would work on MySQL 4.1.9 but not on 4.1.11 or 5.03 it would be much appreciated:

SELECT member.netid, member.fname, member.lname, status.excused, status.unexcused, status.pending, strikes.modstrikes FROM member LEFT JOIN ((SELECT netid, COUNT(CASE WHEN status = 'excused' THEN 1 ELSE NULL END) as excused, COUNT(CASE WHEN status = 'unexcused' THEN 1 ELSE NULL END) as unexcused, COUNT(CASE WHEN status = 'pending' THEN 1 ELSE NULL END) as pending FROM attendance GROUP BY netid) as status) ON member.netid = status.netid LEFT JOIN strikes ON status.netid=strikes.netid WHERE (member.status='active' OR member.status='neophyte') AND member.print='Y' ORDER BY member.lname

4.1.11 spits out the error message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as status) ON member.netid = status.netid LEFT JOIN strikes ON status.netid=stri' at line 1

I uninstalled 5.03 thinking this was a bug but the error message there was something along the lines of a duplicate key found on key 1 and referenced the same (but random) netid found in the attendance table which is aliased as status.

Thanks for any help!

Options: ReplyQuote


Subject
Written By
Posted
Query works in 4.1.9 but dies on 4.1.11 and 5.03
April 08, 2005 07:29AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.