[thelist] Problems with aggregate functions in mySQL

Jonathan Thomas jonathan at opalise.co.uk
Thu May 11 11:00:26 CDT 2006


Hi,

Consider the following hypothetical SQL query, which shows the name of each
archer who has more than one crossbow, along with exactly how many crossbows
he/she has.

select a.archerid, a.archername, c.archerid, count(c.crossbowid) 
from archer a, crossbow c
where a.archerid = c.archerid group by a.archerid, a.archername, c.archerid

If I make a subtle alteration to the third line of the query (note the
asterisk), the query now shows all archers, regardless of whether or not
they have any crossbows:

select a.archerid, a.archername, c.archerid, count(c.crossbowid) 
from archer a, crossbow c
where a.archerid *= c.archerid group by a.archerid, a.archername, c.archerid

Or at least, that's how it works in MS SQL Server. mySQL doesn't like the
syntax and throws up an error message. How would I go about replicating this
behaviour in mySQL?

Many thanks,

Jon





More information about the thelist mailing list