Geek Answers Handbook

Multiple SQL joins from the same table with Codeigniter

I have a DB with 2 identifier columns that reference the same table

How can I make 2 joins in one table and pull data.

Here is what I have:

$this->db->select('s.id, c.title, c.description, s.time ,s.day,s.instructor_change,s.studio_change,s.time_change,s.new_class,s.reservation_req,s.easy_does_it,s.mind,s.level,s.duration,s.location,i.first,i.last');
            $this->db->from('schedule as s');
            $this->db->join('instructors as i', 'i.id = s.instructor_id','inner');
            $this->db->join('classes as c', 'c.id = s.class_id');
            $this->db->where('s.active', '1');
            $this->db->where('s.day', $dayofweek);
            $this->db->order_by('s.time',"ASC");

            $query = $this->db->get();

I also need to pull something like this $ this-> db-> join ('instructors like i', 'i.id = s._alt_instructor_id ');

How can I join the same table twice, but be able in this case to repeat the first and last name for another identifier in the same record?

Answer

$this->db->select('s.id, c.title, c.description, s.time ,s.day,s.instructor_change,s.studio_change,s.time_change,s.new_class,s.reservation_req,s.easy_does_it,s.mind,s.level,s.duration,s.location,i.first,i.last,a.first as alt_first,a.last as alt_last');
            $this->db->from('schedule as s');
            $this->db->join('instructors as i', 'i.id = s.instructor_id','left');
            $this->db->join('instructors as a', 'a.id = s.alt_instructor_id','left');
            $this->db->join('classes as c', 'c.id = s.class_id');
            $this->db->where('s.active', '1');
            $this->db->where('s.day', $dayofweek);
            $this->db->order_by('s.time',"ASC");

            $query = $this->db->get();
+5
source share
2 answers

Just enter it differently:

$this->db->join('instructors as ialt', 'ialt.id = s._alt_instructor_id');
+6
source 
$this->db->select('*');
    $this->db->from('jobs');
    $this->db->join('company','company.cmp_name = jobs.cmp_name');
    $this->db->where('company.location', $data['location']);**strong text**
0

More articles:


All Articles