[thelist] Problems with aggregate functions in mySQL

[John Hicks]

Jonathan Thomas wrote:
> 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?

A left join on crossbows will show all archers.

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

--J