[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