Query question: Multiple joins to the same table

[Mike Toal]

Dear all,

I am trying to create a fantasy football database. There are four tables:

- data (containing the points per match for the players);
- players (containing details of the players);
- users (containing details of the competitors entering the competition); and
- weekRef (contains the week number, player and user id's)

I've included the results of show create table below.

To account for the fact that different users will transfer players during the year, I use the weekRef table to keep track of which user owns which player for a set week.

I want to write a query that returns the sum of points per week for a user. However my lack of a Mysql skillset is evident as I can't get the query to return the correct result.

I have tried to simplify my query by only returning the sum of points for each player using the following code:

select weekRef.week_no, weekRef.player_id, sum(data.points)
from weekRef
inner join data on weekRef.player_id = data.player_id
inner join data data2 on weekRef.week_no = data2.week
group by weekRef.week_no

For my dummy dataset this should return

1 - 14 - 2
1 - 14 - 3

However what it is actually returning is:

1 - 14 - 40062
1 - 14 - 36564

I am obviously not writing my query correctly and I suspect I have committed a cardinal SQL sin with my joins.

I would be most grateful if someone could shed some light on where I have erred in the above query and also suggest how I would extend the query to return the sum of points for a user for each week as my current attempt below is painfully wrong.

select weekRef.week_no, users.user_name, sum(data.points)
from weekRef
inner join data on weekRef.player_id = data.player_id
inner join data data2 on weekRef.week_no = data2.week
inner join users on weekRef.user_id = users.user_id
inner join players on users.user_id = players.user_id
group by weekRef.week_no

Kind regards,

Mike


Database details:
Version - 5.5.36-cll

data: CREATE TABLE `data` (
 `data_id` int(11) NOT NULL,
 `player_id` int(11) NOT NULL,
 `date` varchar(25) NOT NULL,
 `week` int(11) NOT NULL,
 `result` varchar(25) NOT NULL,
 `minutes_played` int(11) NOT NULL,
 `goals_scored` int(11) NOT NULL,
 `assists` int(11) NOT NULL,
 `clean_sheets` int(11) NOT NULL,
 `goals_conceded` int(11) NOT NULL,
 `own_goals` int(11) NOT NULL,
 `penalties_saved` int(11) NOT NULL,
 `penalties_missed` int(11) NOT NULL,
 `yellow_cards` int(11) NOT NULL,
 `red_cards` int(11) NOT NULL,
 `saves` int(11) NOT NULL,
 `bonus` int(11) NOT NULL,
 `EA_Spots_PPI` int(11) NOT NULL,
 `net_transfers` int(11) NOT NULL,
 `tbc` int(11) NOT NULL,
 `value` int(11) NOT NULL,
 `points` int(11) NOT NULL,
 PRIMARY KEY (`data_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

players:CREATE TABLE `players` (
 `player_id` int(11) NOT NULL,
 `first_name` varchar(25) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `last_name` varchar(25) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `web_name` varchar(25) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `position` varchar(25) NOT NULL,
 `team` varchar(25) NOT NULL,
 `user_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`player_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

users:CREATE TABLE `users` (
 `user_id` int(11) NOT NULL AUTO_INCREMENT,
 `user_name` text NOT NULL,
 PRIMARY KEY (`user_id`),
 KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1

weekRef:CREATE TABLE `weekRef` (
 `Ref_id` int(11) NOT NULL AUTO_INCREMENT,
 `week_no` int(11) NOT NULL,
 `user_id` int(11) NOT NULL,
 `player_id` int(11) NOT NULL,
 PRIMARY KEY (`Ref_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1