如何在Codeigniter和mysql中进行此查询？ [重复]

Question

This question already has an answer here:

• Query for post and tags in same query 3 answers

Suppose there are two tables i.e product and product_status

here is the structure of product table

-------------------------------------------------
|   ID   |   PRODUCT_NAME   |    PRODUCT_DESC   |
-------------------------------------------------
|   1    |      abc         |    abc desc       |
-------------------------------------------------
|   2    |      xyz         |    xyz desc       |
-------------------------------------------------

here is the structure of product_status table

-------------------------------------------------
|   ID   |   PRODUCT_ID     |    PRODUCT_STATUS |
-------------------------------------------------
|   1    |        1         |      status 1     |
-------------------------------------------------
|   2    |        1         |      status 2     |
-------------------------------------------------

If I make a query through php (codeigniter) then it will show the data like this.

$this->db->where('ID',1);
$query = $this->db->get('product');
$product = $query->row();

echo $product->PRODUCT_NAME;

But my question is how to get status from product_status table for that specific product id without doing another query? I mean I want a single query which will fetch a single record (because id is unique) and show the product data from product table as well as product status from product_status table as obejct array like this-

$products = $product->PRODUCT_STATUS;

So I can loop through to $products and print the status.

foreach ($products as $p) {
    echo $p;
}

Is it possible without making another query?

</div>

Answer 1

$sql = sprintf("SELECT p.*, ps.PRODUCT_STATUS FROM product p LEFT JOIN product_status ps ON(p.ID = ps.PRODUCT_ID) WHERE p.ID=%d", $product_id);
$query = $this->db->query($sql);
$product = $query->row();