php - 如何在sql或codeigniter中与多个表交互？

Question

我想与 codeigniter 中的 4 个表进行交互。我尝试过但没有结果。我的表格看起来像:

----university----     ----------department----------    -------language--------
| id |  name     |     | id |         name          |    | id |    name        |
------------------     ----------------------------     -----------------------
| 1  | Oxford    |     | 1  | Computer Engineering  |    | 1  |  English       |
| 2  | Harvard   |     | 2  | International Affairs |    | 2  |  German        |

这些表的行只是示例。我的第四个表包含有关其他表的数据。

  --------------------------result----------------------------
  | result_id | university_id | department_id | language_id  |
  ------------------------------------------------------------
  |    1      |    4          |       6       |      8       |
  |    2      |    4          |       7       |      4       |  

我想在同一行显示大学名称，部门名称和语言，其中语言id=2。我尝试了该代码，但没有任何结果:

$this->db->select('university.name,department.name,language.name' );
$this->db->from ( 'university' );
$this->db->from ( 'department' );
$this->db->from ( 'language' );
$this->db->join ( 'result', 'result.university_id = university.id', 'inner' );
$this->db->join ( 'result', 'result.department_id = department.id', 'inner' );
$this->db->join ( 'result', 'result.language_id   = language.id', 'inner'   );
$this->db->where( 'result.language_id', '2' );
$this->db->order_by( 'department.name', 'asc' );

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

return $query->result ();    

Answer 1

尝试制作 from/join 结构

$this->db->select('university.name,department.name,language.name' );
$this->db->from ( 'university' );
$this->db->join ( 'result', 'result.university_id = university.id', 'inner' );
$this->db->from ( 'department' );
$this->db->join ( 'result', 'result.department_id = department.id', 'inner' );
$this->db->from ( 'language' );
$this->db->join ( 'result', 'result.language_id   = language.id', 'inner'   );

$this->db->where( 'result.language_id', '2' );
$this->db->order_by( 'department.name', 'asc' );

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

return $query->result ();  

如果不起作用，请尝试进行 3 个不同的查询并全部合并!