多个SQL连接来自同一个表codeigniter [英] Multiple SQL Joins from same table with Codeigniter

问题描述

我有身份证的引用相同表2列的数据库

我怎样做2加入到同一个表，并能够提取数据。

下面是我：

  $这个 - ＆GT; DB-＆GT;选择（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');
            $这 - ＆GT; DB-肽从（'时间表为s'）;
            $这个 - ＆GT; DB-＆GT;加入（'教官为我'，'i.id = s.instructor_id'，'内'）;
            $这个 - ＆GT; DB-＆GT;加入（类为c'，'c.id = s.class_id'）;
            $这 - ＆GT; DB-化合物其中（'s.active'，'1'）;
            $这个 - ＆GT; DB-化合物其中（s.day'，$星期几）;
            $这个 - ＆GT; DB-＆GT; ORDER_BY（'s.time，ASC）;

            $查询= $这个 - ＆GT; DB-＆GT;获得（）;
 

我还需要拉这样的事情 $这个 - > DB->加入（'教官为我'，'i.id = s._alt_instructor_id ）;

我如何加入同一个表两次，但能够再次拉在这种情况下，第一个和最后一个名字不同的ID在同一记录？

答案

  $这个 - ＆GT; DB-＆GT;选择（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作为alt_first，a.last作为alt_last'）;
            $这 - ＆GT; DB-肽从（'时间表为s'）;
            $这个 - ＆GT; DB-＆GT;加入（'教官为我'，'i.id = s.instructor_id'，'左'）;
            $这个 - ＆GT; DB-＆GT;加入（'教官作为'，'a.id = s.alt_instructor_id'，'左'）;
            $这个 - ＆GT; DB-＆GT;加入（类为c'，'c.id = s.class_id'）;
            $这 - ＆GT; DB-化合物其中（'s.active'，'1'）;
            $这个 - ＆GT; DB-化合物其中（s.day'，$星期几）;
            $这个 - ＆GT; DB-＆GT; ORDER_BY（'s.time，ASC）;

            $查询= $这个 - ＆GT; DB-＆GT;获得（）;
 

解决方案

只是别名不同的看法：

  $这个 - ＆GT; DB-＆GT;加入（'教官为IALT'，'ialt.id = s._alt_instructor_id'）;
 

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

How do I do 2 joins to the same table, and be able to pull the 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 need to also pull something like this $this->db->join('instructors as i', 'i.id = s._alt_instructor_id');

How do I join the same table twice, but be able to pull the in this case the first and last name again for a different ID on 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();

解决方案

Just alias it differently:

$this->db->join('instructors as ialt', 'ialt.id = s._alt_instructor_id');

这篇关于多个SQL连接来自同一个表codeigniter的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！