By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,610 Members | 2,332 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,610 IT Pros & Developers. It's quick & easy.

How to compare several rows in one table???

P: 1
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
Expand|Select|Wrap|Line Numbers
  1.                                   Table  "Data"
  2. -------------------------------------------------------------------------------------------------
  3. ID     HN    HID    AN        ANID        Event           TEAMID     DATE
  4. 11111   a      5      b         3          1                 3    15/08/2007
  5. 11111   a       5      b         3          4                 5    15/08/2007
  6. 11111   a       5      b         3          1                 5    15/08/2007
  7.  
  8.  
  9. 22222   c      7      d         4          1                 4    11/08/2007
  10. 22222   c      7      d         4          1                 7    11/08/2007
  11. 22222   c      7      d         4          1                 7    11/08/2007
  12. ________________________________________________________
  13.  
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
Expand|Select|Wrap|Line Numbers
  1.                                          Table "Result"
  2.  
  3. -----------------------------------------------------------------------------------------
  4. ID     HN        Hgoal       AN    Agoal       DATE
  5. 11111   a         1          b       2         15/08/2007
  6. 22222   c         2          d       1         11/08/2007
  7. ___________________________________________________
  8.  

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)
Sep 6 '07 #1
Share this Question
Share on Google+
1 Reply


P: 9
Hello :

Try to use a function as decode or case when to translate the event and group it for match id.
Sep 19 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.