remyremy remyremy - 5 months ago 7
MySQL Question

mysql JOIN query returns multiple times the same rows

I'm trying to display all the generic slopes (from

game_slopes
) from sector 1 that are built (i.e. present in
game_created_slopes
) and status_id =1.

CREATE TABLE `game_created_slopes` (
`id_created_slopes` int(11) NOT NULL,
`id_player` int(11) NOT NULL,
`id_slope` int(11) NOT NULL,
`custom_name` varchar(45) DEFAULT NULL,
`slope_condition` int(3) NOT NULL,
`id_status` int(11) NOT NULL,
`end_construction` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `game_created_slopes` (`id_created_slopes`, `id_player`, `id_slope`, `custom_name`, `slope_condition`, `id_status`, `end_construction`) VALUES
(168, 46, 6, 'Slope 24', 50, 1, '2016-05-17 17:01:25'),
(170, 46, 1, 'Slope 1', 1, 1, '2016-06-06 18:35:22'),
(172, 46, 7, 'Slope 3', 100, 1, '2016-06-08 21:48:43');


CREATE TABLE `game_slopes` (
`id_slope` int(11) NOT NULL,
`id_sector` int(11) NOT NULL,
`name_english` varchar(45) NOT NULL,
`name_french` varchar(45) DEFAULT NULL,
`length` int(11) NOT NULL,
`id_difficulty` int(11) NOT NULL,
`cost` int(11) NOT NULL,
`building_time` int(11) NOT NULL,
`reputation` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `game_slopes` (`id_slope`, `id_sector`, `name_english`, `name_french`, `length`, `id_difficulty`, `cost`, `building_time`, `reputation`) VALUES
(1, 1, 'Slope 1', 'Piste 1', 2000, 1, 1000000, 13, 3000),
(6, 1, 'Slope 2', 'Piste 2', 1000, 2, 100000, 15, 5000),
(7, 2, 'Slope 3', 'Piste 3', 1400, 3, 200000, 5, 8000),
(8, 1, 'Slope 5', 'Piste 5', 1456, 4, 105000, 20, 5040);


Unfortunately the two results that should be returned (ID 168 and 170) are returned three times each. I noticed that if
game_created_slopes
contains 5 rows, then the two IDs will be returned 5 times each.

(ID 172 is in sector 2 so it's not returned)

My query:

$this->db->distinct('game_slopes.id_slope, game_slopes.id_sector, game_created_slopes.id_slope, game_created_slopes.id_created_slopes, game_created_slopes.id_player');
$this->db->from('game_slopes, game_created_slopes');
$this->db->join('game_created_slopes as created_slopes_tbl', 'game_slopes.id_slope = created_slopes_tbl.id_slope', 'inner');
$this->db->where('created_slopes_tbl.id_status', '1');
$this->db->where('game_slopes.id_sector', '1);
$this->db->where('created_slopes_tbl.id_player', $currentUserID);
$query = $this->db->get();


PHP code:

$num_slopes_for_this_sector = $this->Model->get_slopes_($currentUserID);

foreach ($num_slopes_for_this_sector->result() as $row){
echo '<br>SECTOR :'. $i;
echo '<br>id_created_slopes:'.$row->id_created_slopes;
}


What is wrong with my query? It should return only two IDs.

Answer

You should use from a single table beacuse you use join for the second

 $this->db->from('game_slopes');

If you leave the two table you obtain a cartesian product for both the table

Comments