笨SQL查询中的连接上两个不同的列

问题描述：

用户：

+----+-------+-------+ 
| 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用户信息，我如何进行该查询？

再添加一个加入连接'created_by' –

你能告诉我如何？ – CodeGodie

答

我是残疾人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

这绝对有帮助。我很感激。我尝试了SQL语句，它的工作原理。我会尝试你的CI代码并实施它。非常感谢你 – CodeGodie

答

是这项工作知府这对我在我的控制器非常有益

public function out() 
    { 
      $selqry = 'p.* , u.items as REAL' ; 
      $selqry .= ', c.items as ITEM' ; 
      $this->db->select($selqry); 
      $this->db->from('outgoing as p'); 
      $this->db->join('financial_items u', 'u.id = p.item_id_real', 'left'); 
      $this->db->join('financial_items c', 'c.id = p.item_id', 'left'); 
      $query = $this->db->get(); 
      $data = $query->result(); 
      echo json_encode($data); 

    }

笨SQL查询中的连接上两个不同的列

相关推荐