I'm running a hockey player database and I'm building up statistics
about player penalties. I extract the penalties like this:
SELECT P.playerid, T.number, count(P.playerid) AS num_penalties,
sum(P.length) AS length_penalties
FROM period_penalties P, troops T
WHERE T.origin=1 AND T.season=2009 AND T.playerid=P.playerid AND NOT
P.opponent AND P.period_id IN (...id_list...)
GROUP BY P.playerid, T.number
and get a result like this:
playerid | number | num_penalties | length_penalties
----------+--------+---------------+------------------
236 | 89 | 9 | 26
267 | 20 | 8 | 37
214 | 14 | 1 | 2
...
This is what I want.
However, there is a special case which I need to fetch as well. The
special case is lines in the table with playerid = 0, which means it's
a team penalty instead of a player penalty. Id 0 does not exist in the
player table (called "troops" in the query above), and that's why that
row does not get included in the result.
The only way I found out how to include the special case row is by
doing a union-query, like this:
SELECT ...as above...
UNION
SELECT P.playerid, Null, count(P.playerid) AS num_penalties,
sum(P.length) AS length_penalties
FROM period_penalties P
WHERE playerid=0 AND NOT P.opponent AND P.period_id IN (...period_ids...)
GROUP BY P.playerid
ORDER BY length_penalties DESC, num_penalties DESC
and the result is like this:
playerid | number | num_penalties | length_penalties
----------+--------+---------------+------------------
20 | 6 | 15 | 103
28 | 4 | 11 | 46
267 | 20 | 8 | 37
265 | 2 | 5 | 31
0 | | 4 | 29
122 | 11 | 4 | 29
...
Which is the actual result I want. However, I find the union-query
horrible and in a way somewhat redundant. But since my JOIN-skills are
too low I am unable to find a better solution than this.
If anyone can shed some light on how to approach this problem better
than my solution I'm all ears.
--
- Rikard - http://bos.hack.org/cv/