Outer join does not give expected result
I have three tables and I want to count the number of records for each resort in each table. I am getting unexpected result that I cannot explain.
My tables are as follows:
CREATE TABLE `game_items` (
`id_items` int(11) NOT NULL,
`id_resort` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `game_items` (`id_items`, `id_resort`) VALUES
(36, 81),
(38, 81),
(39, 67);
CREATE TABLE `game_slopes` (
`id_slopes` int(11) NOT NULL,
`id_resort` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `game_slopes` (`id_slopes`, `id_resort`) VALUES
(16, 81);
CREATE TABLE `game_staff` (
`id_staff` int(11) NOT NULL,
`id_resort` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `game_staff` (`id_staff`, `id_resort`) VALUES
(1, 69),
(3, 67),
(5, 81),
(7, 81),
(8, 81),
(12, 81);
CREATE TABLE `game_resorts` (
`id_resort` int(11) NOT NULL,
`id_player` int(11) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `game_resorts` (`id_resort`, `id_player`) VALUES
(66, 59),
(67, 60),
(68, 61),
(69, 62),
(70, 63),
(81, 67),
(82, 68);
And my request:
SELECT `game_players_tbl`.`id_player`, `game_resorts`.`id_resort`,
COUNT(game_items_tbl.id_items) as item_count,
COUNT(game_slopes_tbl.id_slopes) as slope_count,
COUNT(game_staff_tbl.id_staff) as staff_count
FROM `game_resorts`
INNER JOIN `game_players` as `game_players_tbl` ON `game_resorts`.`id_player` = `game_players_tbl`.`id_player`
LEFT OUTER JOIN `game_items` as `game_items_tbl` ON `game_resorts`.`id_resort` = `game_items_tbl`.`id_resort`
LEFT OUTER JOIN `game_slopes` as `game_slopes_tbl` ON `game_resorts`.`id_resort` = `game_slopes_tbl`.`id_resort`
LEFT OUTER JOIN `game_staff` as `game_staff_tbl` ON `game_resorts`.`id_resort` =`game_staff_tbl`.`id_resort`
GROUP BY `game_resorts`.`id_resort`
ORDER BY `game_resorts`.`reputation` DESC
Result:
id_player id_resort item_count slope_count staff_count
61 68 0 0 0
63 70 0 0 0
67 81 8 8 8
68 82 0 0 0
62 69 0 0 1
59 66 0 0 0
60 67 1 0 1
But I would expect:
id_player id_resort item_count slope_count staff_count
61 68 0 0 0
63 70 0 0 0
67 81 2 1 4
68 82 0 0 0
62 69 0 0 1
59 66 0 0 0
60 67 1 0 1
I don't understand why I am getting 8 in every bill for resort ID 81. I've tried different options but never got the right result.
Edit: Added game_resorts
source to share
The main problem you are facing is your table game_items
has multiple entries for game_resorts
. This forces you to duplicate all of your data that is joined to the table game_resorts
. As @Jorge Campos stated, it would be better if you create separate counts for each table and then join them to your resorts table.
SQL Query
SELECT `game_players_tbl`.`id_player`, `game_resorts`.`id_resort`,
game_items_tbl.Count AS item_count,
game_slopes_tbl.Count AS slope_count,
game_staff_tbl.Count AS staff_count
FROM `game_resorts`
INNER JOIN `game_players` AS `game_players_tbl` ON `game_resorts`.`id_player` = `game_players_tbl`.`id_player`
LEFT OUTER JOIN (
SELECT `game_items`.`id_resort`, COUNT(`game_items`.`id_items`) AS Count FROM `game_items` GROUP BY `game_items`.`id_resort`
) AS `game_items_tbl` ON `game_resorts`.`id_resort` = `game_items_tbl`.`id_resort`
LEFT OUTER JOIN (
SELECT `game_slopes`.`id_resort`, COUNT(`game_slopes`.`id_slopes`) AS Count FROM `game_slopes` GROUP BY `game_slopes`.`id_resort`
) AS `game_slopes_tbl` ON `game_resorts`.`id_resort` = `game_slopes_tbl`.`id_resort`
LEFT OUTER JOIN (
SELECT `game_staff`.`id_resort`, COUNT(`game_staff`.`id_staff`) AS Count FROM `game_staff` GROUP BY `game_staff`.`id_resort`
) AS `game_staff_tbl` ON `game_resorts`.`id_resort` =`game_staff_tbl`.`id_resort`
GROUP BY `game_resorts`.`id_resort`
ORDER BY `game_resorts`.`reputation` DESC
Edit: Instead of being lazy, I went and did a count of all the other tables.
Edit: Fixed the last subquery to correct the table as @remyremy pointed out
source to share