php - mysql JOIN 查询多次返回相同的行

Question

我正在尝试显示扇区 1 中已构建(即存在于 game_created_slopes 中)且 status_id =1 的所有通用斜坡(来自 game_slopes)。

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);

不幸的是，应该返回的两个结果(ID 168 和 170)各返回三次。我注意到，如果 game_created_slopes 包含 5 行，那么这两个 ID 将各返回 5 次。

(ID 172 位于扇区 2，因此不会返回)

我的查询:

$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 代码:

$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;
}

我的查询有什么问题？它应该只返回两个 ID。

Answer 1

您应该从单个表中使用，因为您使用第二个表的连接

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

如果您离开两个表，您将获得两个表的笛卡尔积