[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