group by data in left join

Question

I have two tables: orders and product_orders there are one id in orders table. i.e: 9 and order_id(as foreign key) in product_orders repeated 3 times. So I want this three entry in product_orders table with one entry in orders table. My query is:

SELECT orders.*,order_products.* FROM orders LEFT JOIN order_products ON orders.id=order_products.order_id

this gives :

My expected result is:

Array
    (
        [0] => Array
            (
                [orders] => Array
                    (
                        [id] => 9
                        [name] => Abdus Sattar Bhuiyan
                        [email] => sattar.kuet@gmail.com
                        [mobile] => 01673050495
                        [alt_mobile] => 01818953250
                        [city_id] => 2
                        [location_id] => 5
                        [status] => No contact
                        [chashed] => NO
                        [created] => 2015-06-27 12:49:34
                        [modified] => 2015-06-27 12:49:34
                        [comment] => 
                    )

                [order_products] => Array
                    (
                          [0] => Array(
                                   [id] => 2
                                   [order_id] => 9
                                   [product_id] => 1
                                   [pieces] => 1
                                )
                           [1] => Array(
                                   [id] => 3
                                   [order_id] => 9
                                   [product_id] => 2
                                   [pieces] => 1
                                )   
                             [2] => Array(
                                  [id] => 4
                                  [order_id] => 9
                                  [product_id] => 3
                                  [pieces] => 1
                                ) 

                    )

            )
    )

    I used GROUP BY orders.id. But no luck.

Renzo · Accepted Answer

You are basically expecting a result that is not possible to obtain with a SQL query: you want a hierarchical structure, with a single order and a set of order_product for that order, while the query returns a flat table, with one row for each order together with the information of a different order_product. This is the way in which SQL works.

A way to solve your problem is to do a sort of post-processing in php, after getting all the rows, group all of them that have the same order part, and produce a new array for the order_product part related to that order.

group by data in left join

Answers (1)

Related Questions