How to compare several rows in one table???

[fengqysll]

Hi:

I am a table holding football match results using DB2

ID = matchid
HN = Home team name
HID = Home team id
AN = Away team name
ANID = Away Team id
Eevent= use 1 for score, 2 for substitution, 3 for booking, 4 for Foul
Teamid = Which team performed "Event (1--->4)"
Date = Match Date

                                  Table  "Data"
-------------------------------------------------------------------------------------------------
ID     HN    HID    AN        ANID        Event           TEAMID     DATE
11111   a      5      b         3          1                 3    15/08/2007
11111   a       5      b         3          4                 5    15/08/2007
11111   a       5      b         3          1                 5    15/08/2007
 
 
22222   c      7      d         4          1                 4    11/08/2007
22222   c      7      d         4          1                 7    11/08/2007
22222   c      7      d         4          1                 7    11/08/2007
________________________________________________________
 

What I want it display is :

ID = matchid
HN = Home team name
Hgoal = Home team Scores
AN = Away team name
Agoal = Away team scorses
( Because team 5 did a own goal (event = 4), the score is 2:1 )
Date = Match Date

                                         Table "Result"
 
-----------------------------------------------------------------------------------------
ID     HN        Hgoal       AN    Agoal       DATE
11111   a         1          b       2         15/08/2007
22222   c         2          d       1         11/08/2007
___________________________________________________
 

I tried use self join, however, still cannot do it, any one PLEASE PLEAE HELP!!!


Cheers

(Really sorry about the format, i tried to make to more readable)

[Remi]

Hello :

Try to use a function as decode or case when to translate the event and group it for match id.