Here is a query that will do what you want I think:
SELECT count(distinct goal.id) as ours, count(distinct goalopp.id) as
theirs, game.id
FROM game LEFT JOIN goalopp ON game.id=goalopp.game
LEFT JOIN goal ON game.id=goal.game
GROUP BY game.id
HAVING ours > theirs;
We have to use a having statement to compare the number of goals since
count() is an aggregate function. They can't be computed as part of the
where clause as the where clause determines which should be counted in the
function.
Left Joins are needed in this case because it is possible that there might
be 0 goals or goalopp for the game, in which case we still want the number
(which would be 0).
Hope that helps you understand some of the logic behind why the query has to
be this way.
Harrison
----- Original Message -----
From: "Adriano Manocchia" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, May 21, 2002 1:38 PM
Subject: Query help
> I can't seem to properly wrap my brain around left joins. If anyone
> knows of a good tutorial somewhere, I'd appreciate it. That having been
> said, I was hoping someone could help with this query I'm trying to do
> as I've been horribly unsuccessful.
>
> 3 tables: goal (own goals), goalopp (opponent goals), game
>
> Pertinent fields:
> goal.id
> goal.game
> goalopp.id
> goalopp.game
> game.id
>
> One game record for each game.
> One goal record for each own goal, one goal record for each opponent
> goal, each with a unique id in their table (goal.id, goalopp.id) and a
> reference to the game id (goal.game, goalopp.game).
>
> What I'm trying to do is get the number of games where the goal count is
> greater than the goalopp count for a set of games. Of course I could
> just loop through an array for each game, but I was hoping to do it with
> a single query. So what I'm getting at is essentially
>
> select (count game) where ((count goal) > (count goalopp)) for each game
> where game.something='whatever'
>
> Help would be greatly appreciated.
>
> Age
>
> ---------------------------------------------------------------
> Adriano "Age" Manocchia [EMAIL PROTECTED]
> Cornell University AIM/Yahoo/MSN: SalsaSharkNet
> http://salsashark.net/age ICQ: 5962736
> ---------------------------------------------------------------
> "The capacity to blunder slightly is the real marvel of DNA.
> Without this special attribute, we would still be anaerobic
> bacteria, and there would be no music." -Lewis Thomas
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
