Codeigniter SQL查询在两个不同的列上联接

debugcn 发表于 Dev

CodeGodie

我有以下表格：

使用者：

+----+-------+-------+
| ID | fname | lname |
+----+-------+-------+
| 1  | Joe   | Doe   |
+----+-------+-------+
| 2  | Jane  | Doet  |
+----+-------+-------+

产品：

+----+------+------------+------------+
| ID | name | created_by | updated_by | //created_by and updated_by are user Ids
+----+------+------------+------------+
| 1  | some | 1          | 1          |
+----+------+------------+------------+
| 2  | some | 1          | 2          |
+----+------+------------+------------+
| 3  | some | 2          | 2          |
+----+------+------------+------------+
| 4  | some | 2          | 1          |
+----+------+------------+------------+

我想创建一个查询，该查询输出下表：

+--------------+------------+------------+
| Product Name | Created By | Updated By |
+--------------+------------+------------+
| Some         | Jane Doet  | Joe Doe    |
+--------------+------------+------------+

到目前为止，我正在使用Join这种方式：

public function getAll() {
    $this->db->select('products.* , users.fname, users.lname');
    $this->db->from('products');
    $this->db->join('users', 'users.id = products.updated_by', 'left');
    $q = $this->db->get();
    return $q->result();
}

产生以下内容：

SELECT products.*, users.fname, users.lname 
FROM `products`
LEFT JOIN users ON users.id=products.updated_by

这样可以正常工作，并且可以获取updated_by用户信息，但是如果我同时需要created和updated用户信息，该如何查询呢？

雷文德·雷迪（Ravinder Reddy）

我有残障，codeigniter但如果我理解您的要求正确，那么以下内容可能会有所帮助。

public function getAll() {
  $selqry = 'p.* , u.fname as fname_ub, u.lname as lname_ub'  
  $selqry .= ', c.fname as fname_cb, c.lname as lname_cb'  

  $this->db->select( $selqry );
  $this->db->from('products as p');
  $this->db->join('users u', 'u.id = p.updated_by', 'left');

  $this->db->join('users c', 'c.id = p.created_by', 'left');

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

  return $q->result();
}

上面的代码应产生以下SQL语句：

SELECT 
       p.*, u.fname as fname_ub, u.lname as lname_ub
       ,    c.fname as fname_cb, c.lname as lname_cb
FROM `products` as p
LEFT JOIN users u ON u.id=p.updated_by
LEFT JOIN users c ON c.id=p.created_by

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。