MySQL Forums :: Newbie :: Compare two rows in same table


Advanced Search

Compare two rows in same table
Posted by: Robert Bruguera ()
Date: July 22, 2009 10:32PM

Hello to all,
I'm trying to compare two row in this table
`standings` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `schedule_id` int(10) unsigned NOT NULL,
  `team_id` int(11) unsigned NOT NULL default '0',
  `score` int(2) NOT NULL default '0',
  PRIMARY KEY  (`id`)

These are final score of games the schedule column will be repeated twice in every game because there two team playing so two games in this table will look like this:

id | schedule_id | team_id | score
1 | 1 | 1 | 5
2 | 1 | 2 | 3
3 | 2 | 3 | 2
4 | 2 | 4 | 3

What I'm trying to achieved is

schedule_id | team1 | win
1 | 1 | 1
1 | 2 | 0
2 | 3 | 0
2 | 4 | 1

and so on and maybe calculate percentage of each team.

This is how far I got but I'm extra rows and if I use GROUP BY I loose some rows
SELECT s1.team_id, s2.score, (
s1.score > s2.score
) AS win
FROM standings AS s1
LEFT JOIN standings AS s2 ON s1.schedule_id = s2.schedule_id


Thank you all in advance.

Options: ReplyQuote


Subject Written By Posted
Compare two rows in same table Robert Bruguera 07/22/2009 10:32PM
Re: Compare two rows in same table laptop alias 07/23/2009 03:45AM
Re: Compare two rows in same table Robert Bruguera 07/23/2009 11:47AM
Re: Compare two rows in same table praba karan 09/02/2009 06:53AM
Re: Compare two rows in same table laptop alias 09/02/2009 06:59AM
Re: Compare two rows in same table Tom Melly 09/02/2009 07:01AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.