stumped: how to tell if multiple rows have matching data

tranphp

Problem:

I'm pulling rows of data: row['team'], row['W'], row['L']

if three teams have the same W-L record, I need to know which teams they are. The only thing I can come up with is to sort the rows by W and L and then comparing one row to the next. There has to be another way, as this doesn't seem too efficient. Any ideas anyone?

bretticus

When I saw this post, I had to try it...

My solution doesn't give numbers, just teams with the same scores. In other words, which teams have the same score as at least one or more teams.

$sql = "SELECT `Team`
FROM `football`
WHERE `TeamID`
IN (

SELECT A.`TeamID`
FROM `football` A, `football` B
WHERE (
A.W = B.W
)
AND (
A.L = B.L
)
AND (
A.`Team` <> B.`Team`
)
)
";

Here's the SQL data schema I used for testing.

CREATE TABLE `football` (
  `TeamID` int(10) unsigned NOT NULL auto_increment,
  `Team` varchar(20) NOT NULL default '',
  `W` int(11) NOT NULL default '0',
  `L` int(11) NOT NULL default '0',
  PRIMARY KEY  (`TeamID`)
) ENGINE=InnoDB;

INSERT INTO `football` (`TeamID`, `Team`, `W`, `L`) VALUES (1, 'Bear', 10, 5);
INSERT INTO `football` (`TeamID`, `Team`, `W`, `L`) VALUES (2, 'Sea Hawks', 6, 7);
INSERT INTO `football` (`TeamID`, `Team`, `W`, `L`) VALUES (3, 'Falcons', 7, 8);
INSERT INTO `football` (`TeamID`, `Team`, `W`, `L`) VALUES (4, 'Raider', 23, 1);
INSERT INTO `football` (`TeamID`, `Team`, `W`, `L`) VALUES (5, 'Cardinals', 7, 8);
INSERT INTO `football` (`TeamID`, `Team`, `W`, `L`) VALUES (6, 'Chiefs', 7, 8);

This all looks a little goofy to me. I swear I've done stuff like this before...oh well. I posted it in case you could use it.

tranphp

I'm glad to see someone out there with a passion for both php/mysql AND football. 🙂

Well, bretticus, it was close, but unfortunately, I was attempting to retrieve all the records and sort them by W-L records. I would then have to sort the matching W-L records further by a tiebreaker, which means if the Cardinals and the Chiefs have a 7-8 record and the Cardinals had beaten the Chiefs, the Cardinals would win the tiebreaker.

I have my tables set up like so.

table game(int gameID)
table teamresult (int teamresultID, int gameID, teamID, W, L)

I query all the teamresults and sum up their W's and L's. Without going into further detail, it gets to be a little too much for me to figure out if there are 3 or more teams with the same record.