[thelist] Problems with aggregate functions in mySQL

Jonathan Thomas jonathan at opalise.co.uk
Thu May 11 11:49:10 CDT 2006


John,

Many thanks. I really should get round to learning and understanding this
new-fangled SQL-92 syntax at some point :)

-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On Behalf Of John Hicks
Sent: 11 May 2006 17:13
To: thelist at lists.evolt.org
Subject: Re: [thelist] Problems with aggregate functions in mySQL

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
-- 

* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester 
and archives of thelist go to: http://lists.evolt.org 
Workers of the Web, evolt ! 







More information about the thelist mailing list