多表Codeigniter中的内连接

Question

我想在codeigniter的crudodel中创建一个显示函数来显示13号发票上的记录。我将在sql上运行以下查询。它按照我的要求工作。现在，我必须使用内连接将这个查询转换为codeigniter。

SELECT b1.Invoice_No,
       cust_name,
       cust_address,
       cust_contact,
       Item_name,
       Item_qty,
       Item_amount
FROM tbl_bill_invoice AS b1
INNER JOIN tbl_billmenu AS b2 ON(b2.Invoice_No = b1.Invoice_No)
INNER JOIN tbl_billcustomer AS b3 ON(b3.cust_id = b1.cust_id)
WHERE b1.Invoice_no = 13

Answer 1

尝尝这个

$this->db->select('b1.Invoice_No, cust_name, cust_address, cust_contact, Item_name, Item_qty, Item_amount');
$this->db->from(' tbl_bill_invoice as b1');
$this->db->join('tbl_billmenu as b2', 'b2.Invoice_No = b1.Invoice_No', 'inner');
$this->db->join('tbl_billcustomer as b3', 'b3.cust_id = b1.cust_id', 'inner');
$this->db->where('b1.Invoice_no', 13);
return $this->db->get()->row();

Answer 2

你看，

$invoice_no = 13;
$this->db->select('b1.Invoice_No, cust_name, cust_address, cust_contact, Item_name, Item_qty, Item_amount');
$this->db->from('tbl_bill_invoice b1');
$this->db->join('tbl_billmenu b2', 'b2.Invoice_No = b1.Invoice_No', 'inner');
$this->db->join('tbl_billcustomer b3', 'b3.cust_id = b1.cust_id', 'inner');
$this->db->where('b1.Invoice_no', $invoice_no);
$query = $this->db->get();
if ($query->num_rows() > 0) {
    return $query->row();
} else {
    return array();
}

Answer 3

只需编写以下代码

$sql = "SELECT b1.Invoice_No,
   cust_name,
   cust_address,
   cust_contact,
   Item_name,
   Item_qty,
   Item_amount
   FROM tbl_bill_invoice AS b1
   INNER JOIN tbl_billmenu AS b2 ON(b2.Invoice_No = b1.Invoice_No)
   INNER JOIN tbl_billcustomer AS b3 ON(b3.cust_id = b1.cust_id)
   WHERE b1.Invoice_no = 13";
$result = $this->db->query($sql)->result();