CODEWITHSUNDEEP
mysqldatabasecodeigniterjoin
may
may

Reputation: 755

using mysql join to show not match value

I have a query to join 3 tables.

$this->db->select("a.user_id as id, a.plate_number as plate_number, a.current_lat as lat, a.current_lon as lon, a.created_on as created_on, a.updated_on as updated_on, a.available as available, a.location_id as location_id, b.user_name as name, b.user_email as email, b.user_phone as phone, c.name as location_name");
        $this->db->from('user_driver as a');
        $this->db->join('user as b', 'a.user_id = b.user_id');
        $this->db->join('vendor_location as c', 'a.location_id = c.location_id');
        $query = $this->db->get();
        $data['driver'] = $query->result_array();

user_driver table structure: user_driver

vendor_location table structure: vendor_location

The result : result

I want to show the rest of user_driver table even though there are not matching value to vendor_location table. The LocationName field could be filled NULL instead of not showing anything at all.

I tried left outer and full outer but it's not working. It left me with the only one row showing.

Upvotes: 0

Views: 281

Answers (2)

A J
A J

Reputation: 4024

I want to show the rest of user_driver table even though there are not matching value to vendor_location table.

For this, you should use LEFT JOIN. It allows you to fetch rows from table in left side even when there is no match in right table.

$this->db->select("a.user_id as id, a.plate_number as plate_number, a.current_lat as lat, a.current_lon as lon, a.created_on as created_on, a.updated_on as updated_on, a.available as available, a.location_id as location_id, b.user_name as name, b.user_email as email, b.user_phone as phone, c.name as location_name");
$this->db->from('user_driver as a');
$this->db->join('user as b', 'a.user_id = b.user_id');
$this->db->join('vendor_location as c', 'a.location_id = c.location_id', 'left');//modify this line
$query = $this->db->get();
$data['driver'] = $query->result_array();

Upvotes: 1

Shibon
Shibon

Reputation: 1574

have u tried like this

$this->db->join('user as b', 'a.user_id = b.user_id', 'LEFT');
    $this->db->join('vendor_location as c', 'a.location_id = c.location_id', 'LEFT');

Upvotes: 0

Related Questions