mysql JOIN query returns multiple times the same rows

Question

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.

Not criticizing, just wondering out of curiosity, why would you build the query that way instead of just a more direct SELECT fields FROM joined tables WHERE conditions met approach? It looks like a fairly simple sql query was chopped up and shuffled. — Uueerdo
– Uueerdo, Commented Jun 9, 2016 at 19:21
I appreciate the comment, actually I'm open to any suggestion to make the query simpler. Do you have an example to provide for such syntax? — remyremy
– remyremy, Commented Jun 9, 2016 at 19:25

ScaisEdge · Accepted Answer · 2016-06-09 19:17:15Z

1

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

edited Jun 9, 2016 at 19:17

answered Jun 9, 2016 at 19:14

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Uueerdo · Accepted Answer · 2016-06-09 20:02:17Z

To show a direct translation, line for line...

SELECT 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
FROM game_slopes, game_created_slopes
INNER JOIN game_created_slopes as created_slopes_tbl ON game_slopes.id_slope = created_slopes_tbl.id_slope
WHERE created_slopes_tbl.id_status = 1
AND game_slopes.id_sector = 1
AND created_slopes_tbl.id_player = $currentUserID
;

I'd format it more like this though (also removing the accidental cross join, and updating the reference/alias used)

SELECT DISTINCT game_slopes.id_slope, game_slopes.id_sector
   , created_slopes_tbl.id_slope, created_slopes_tbl.id_created_slopes
   , created_slopes_tbl.id_player
FROM game_slopes
    INNER JOIN game_created_slopes AS created_slopes_tbl 
       ON game_slopes.id_slope = created_slopes_tbl.id_slope
WHERE created_slopes_tbl.id_status = 1
   AND game_slopes.id_sector = 1
   AND created_slopes_tbl.id_player = $currentUserID
;

Finally, though I am not completely familiar with php; I'd replace the $currentUserID with ? and turn it into a parameterized query.

Though, I'm a lazy typist (and have had aliases be helpful later when tables need replaced with subqueries).

SELECT DISTINCT gs.id_slope, gs.id_sector
   , gcs.id_slope, gcs.id_created_slopes
   , gcs.id_player
FROM game_slopes AS gs
    INNER JOIN game_created_slopes AS gcs
       ON gs.id_slope = gcs.id_slope
WHERE gcs.id_status = 1
   AND gs.id_sector = 1
   AND gcs.id_player = $currentUserID
;

Collectives™ on Stack Overflow

mysql JOIN query returns multiple times the same rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related