Re: count(1) return 0?

Zak McGregor <zak(at)mighty(dot)co(dot)za> writes:

> in fixture 4916, player 1200 won 9-0, but his opponent does not show as having 0
> in the second result set. I suspect what I am expecting is somehow illogical,
> but I can't see why.

It's illogical because the database would have no idea what fixture groups to
include if they don't appear in the results table. It doesn't know what the
range of possible values are.

What you would have to do is do an outer join against an exhaustive list of
possible fixture values and then do a count(results.fixture) or something like
that.

So for example something like:

SELECT count(results.fixture) AS home_wins, home
FROM fixtures
LEFT OUTER JOIN results USING (fixture)
WHERE winner=home
GROUP BY fixture,home

--
greg