JOIN problem

[eluser]underzen[/eluser]
My brain is fried and I'm having a problem. I have 2 tables: orders and orders_items. The orders table contains the following fields: id, order_date. The orders_items table contains the following fields: id, order_id, product_id. What I want to do is for every id in the orders table join it with order_id in the orders_items table.

The query looks like this:

Code:

SELECT `orders`.`id`, `orders`.`order_date`, `orders_items`.`order_id`, `orders_items`.`product_id`
FROM (`orders`)
JOIN `orders_items` ON `orders_items`.`order_id` = `orders`.`id`
WHERE `orders`.`customer_id` = '19'

The code looks like this:

Code:

$query = $this->db->select('orders.id, orders.order_date, orders_items.order_id, orders_items.product_id')->from('orders')->join('orders_items','orders_items.order_id = orders.id' ,'')->where('orders.customer_id' , 19)->get();

The output that I get is this:

Code:

Array
(
    [0] => Array
        (
            [id] => 84
            [order_date] => 2010-01-04 15:07:00
            [order_id] => 84
            [product_id] => 1
        )

    [1] => Array
        (
            [id] => 84
            [order_date] => 2010-01-04 15:07:00
            [order_id] => 84
            [product_id] => 3
        )

    [2] => Array
        (
            [id] => 85
            [order_date] => 2010-01-07 13:24:02
            [order_id] => 85
            [product_id] => 1
        )

)

The result I am trying to get is for 2 orders to be returned with the multiple associated product_id's from the orders_details table. So for order id 84 i want return the two product_id's from the orders_details table of 1 & 3. See below for an idea of the result I'm looking for.

Code:

Array
(
    [0] => Array
        (
            [id] => 84
            [order_date] => 2010-01-04 15:07:00
            [order_id] => 84
            [product_id] => 1
            [product_id] => 3
        )

  

    [1] => Array
        (
            [id] => 85
            [order_date] => 2010-01-07 13:24:02
            [order_id] => 85
            [product_id] => 1
        )

)

My brain is so fried I'm not sure if this post will make sense to you all.

Thanks so much!!!!!

Eric