独特的MySQL加入

Question

Hoping someone can help me with a MYSQL query

I have 3 database tables:

• points_of_interest
• point_of_interest_type
• poi_poi_types

The points_of_interest table contains all details about an area.

The point_of_interest_type table is a list of types e.g Restaurant, Cafe, Museum etc. This table also has an active column (1 or 0)

The poi_poi_types is a mapping table - so it contains the id of the point of interest and the id of the point of interest type

A Point of Interest can have many Point of Interest types so potentially can have multiple entries in the poi_poi_types table

So the results I want are to return a list of points_of_interest but only for active point of interest types

This is the codeignitor query I have so far but its not correct - it is returning every instant of the actual point of interest that is in the poi_poi_types table

    $this->db->select('poi.*, poi_types.type as poi_type');
    $this->db->from('poi');
    $this->db->join('poi_poi_types', 'poi.id = poi_poi_types.poi_id');
    $this->db->join('poi_types', 'poi_types.id = poi_poi_types.poi_type_id');
    $this->db->where('poi_types.active', 1);

Answer 1

I believe this should work. Well, for setting it to make the select statement distinct. I think this would essentially do SELECT DISTINCT

$this->db->distinct();
$this->db->select('poi.*, poi_types.type as poi_type');
$this->db->from('poi');
$this->db->join('poi_poi_types', 'poi.id = poi_poi_types.poi_id');
$this->db->join('poi_types', 'poi_types.id = poi_poi_types.poi_type_id');
$this->db->where('poi_types.active', 1);