I would like to, in one query select orders and have their items attached to them (right now I'm selecting the orders, then using a separate query selecting the order_items - this is proving very slow on a large amount of orders...
orders: id | name | total
order_items: id | order_id | price | qty
order_items_info: id | order_id | order_item_id | tracking_no
The last thing I want to do is: add my order_items_info table to the item array.
$orders = array(
array(
'id' => '',
'name' => '',
'items' => array(
array(
'order_item_id' => '',
'price' => '',
'qty' => '',
'item_info' => array()
),
array(
'order_item_id' => '',
'price' => '',
'qty' => '',
'item_info' => array()
),
...
)
)
);
SELECT o.id,name,total,item_info,price,qty FROM orders o
JOIN order_items oi ON o.id=oi.order_id
JOIN order_items_info oii ON oii.order_id=o.id
AND oii.order_item_id=oi.id
Just a wild guess until you post your table info.
select * from orders join order_items on (orders.id = order_id)