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.