Results 1 to 1 of 1
Thread: Gathering Data
Mar 8, 2009, 16:45 #1
- Join Date
- Jul 2007
- San Jose, California
- 0 Post(s)
- 0 Thread(s)
So I am trying to setup a scoring system for a tennis website. The Following tables are giving me a bit of trouble
CREATE TABLE `individual_match` ( `individual_match_id` int(11) NOT NULL auto_increment, `position_types_type_id` int(11) NOT NULL, `opponent_1` int(11) NOT NULL, `opponent_2` int(11) NOT NULL, `score` varchar(20) NOT NULL, `opponent_1_win` tinyint(1) NOT NULL, `rank` int(11) default NULL, PRIMARY KEY (`individual_match_id`) ) CREATE TABLE `position_types` ( `position_types_id` int(11) NOT NULL auto_increment, `type` varchar(50) NOT NULL, PRIMARY KEY (`position_types_id`) ) CREATE TABLE `doubles_team` ( `doubles_team_id` int(11) NOT NULL auto_increment, `player_1` int(11) NOT NULL, `player_2` int(11) NOT NULL, PRIMARY KEY (`doubles_team_id`) )
Now the problem that I'm having is that position_types varies from doubles or singles, and some other options that aren't extremely important. The problem is that opponent_* can either be a doubles_team_id or just a player_id, whether its a singles match or a doubles match so based off the position_types_id. So what I think I want is a conditional in my sql, but I'm not very good at them. I've written two queries that get the information, but I'd like to combine it into one query.
SELECT t1.name AS `player_1` , t2.name AS `player_2` , score, opponent_1_win FROM individual_match INNER JOIN player t1 ON ( t1.player_id = individual_match.opponent_1 ) INNER JOIN player t2 ON ( t2.player_id = individual_match.opponent_2 ) WHERE individual_match.individual_match_type_id =1; SELECT dt1.player_1, p1.name, dt1.player_2, p2.name, dt2.player_1, p3.name, dt2.player_2, p4.name, score, opponent_1_win FROM individual_match INNER JOIN (doubles_team dt1, player p1, player p2) ON (dt1.doubles_team_id = opponent_1 AND p1.player_id = dt1.player_1 AND p2.player_id = dt1.player_2) INNER JOIN (doubles_team dt2, player p3, player p4) ON (dt2.doubles_team_id = opponent_2 AND p3.player_id = dt2.player_1 AND p4.player_id = dt2.player_2) WHERE individual_match.individual_match_type_id = 2
If I have been unclear just ask me. If you have any suggestions on just optimizing these queries, or even updating the database design.