I have a table of hockey players with goals and penalty minutes in a MySQL database. I want to display a list of players in the roster (using the table 'players') along with a count of all goals and penalties for each player. However, I seem to be getting an error in my results, and I can't figure this out. I have a sample setup of my structure as well as the SQL statement I used to count goals and penalties. Players: (I have two sample players) ------- Playerid name -------- ------- 1 John 2 Dave Goals: (John scores 2 goals while Dave scores 1) ----- Goalid Playerid ------ -------- 1 1 2 1 3 2 Penalties: (John and Dave take 1 penalty each) --------- penaltyid playerid --------- -------- 1 1 2 2 SQL STATEMENT: SELECT players.playerid,name, count(goals.goalid) AS goals, count(penalties.penaltyid) AS penalties FROM players LEFT JOIN goals ON (players.playerid=goals.playerid) LEFT JOIN penalties ON (players.playerid=penalties.playerid) GROUP BY players.playerid; it gives me the following results: +----------+------+-------+-----------+ | playerid | name | goals | penalties | +----------+------+-------+-----------+ | 1 | John | 2 | 2 | | 2 | Dave | 1 | 1 | +----------+------+-------+-----------+ Notice that John has TWO PENALTIES counted, instead of the ONE that he should be having. Why is this happening? I removed the counting and instead just listed out the records with the following SQL modification: SELECT players.playerid,name, goals.goalid, penalties.penaltyid FROM players LEFT JOIN goals ON (players.playerid=goals.playerid) LEFT JOIN penalties ON (players.playerid=penalties.playerid); results in: +----------+------+--------+-----------+ | playerid | name | goalid | penaltyid | +----------+------+--------+-----------+ | 1 | John | 1 | 1 | | 1 | John | 2 | 1 | | 2 | Dave | 3 | 2 | +----------+------+--------+-----------+ Notice that john's single penalty is counted twice. What's wrong with my joining statement?? I'd appreciate any help in this, please respond in the list, not through email. ---------------------------------------------------------------------------- MySQL/SQL insert statements used (if you wanted to test this out quickly in MySQL): mysqladmin create test mysql test drop table if exists players; drop table if exists goals; drop table if exists penalties; create table players (playerid int(3) not null auto_increment, name varchar(30), primary key (playerid)); create table goals (goalid int(3) not null auto_increment, playerid int(3) not null, primary key (goalid,playerid)); create table penalties (penaltyid int(3) not null auto_increment, playerid int(3) not null, primary key (penaltyid,playerid)); insert into players values (1,'John'),(2,'Dave'); insert into goals values (1,1),(2,1),(3,2); insert into penalties values (1,1), (2,2);
