0

I have three tables orders, ordered_dishes, ordered_dish_options.

  • Orders table contains order info like customer name, address, etc.

  • ordered_dishes table contains order_id and dish_id and a unique for every order ordered_dish_id. ordered_dish_id is there because there can be different combinations of the same dish+dish_options. And amount which is the amount of the same combination that is ordered

  • ordered_dish_options contains order_id, ordered_dish_id (fk to ordered_dishes.ordered_dish_id), dish_option_id
    • orders
------
order_id | customer_name
1        | John smith

ordered_dishes
--------------
order_id | ordered_dish_id | dish_id | amount
1        | 1               | 3       | 1
1        | 2               | 3       | 2
1        | 3               | 6       | 1

ordered_dish_options
--------------------
order_id | ordered_dish_id | dish_option_id
1        | 1               | 2
1        | 2               | 4
1        | 3               | 3

    note: in this example there are two dishes with dish_id=3 but the dish+dish_option combination is different.

    I have the following query:

    "SELECT orders.*,
GROUP_CONCAT(ordered_dishes.dish_id SEPARATOR ', ') dish_ids,
GROUP_CONCAT(ordered_dishes.amount SEPARATOR ', ') dish_amounts,
GROUP_CONCAT(ordered_dishes.ordered_dish_id SEPARATOR ', ') dish_odi,
GROUP_CONCAT(ordered_dish_options.ordered_dish_id SEPARATOR ', ') do_odi,
GROUP_CONCAT(ordered_dish_options.dish_option_id SEPARATOR ', ') dish_option_ids
FROM orders
LEFT JOIN ordered_dishes ON orders.order_id=ordered_dishes.order_id 
LEFT JOIN ordered_dish_options ON orders.order_id=ordered_dish_options.order_id 
WHERE orders.order_id=1"

    this query gives my this:

    order_id, 
orderdata, 
dish_ids='3,3,6,3,3,6,3,3,6', 
dish_amounts='1,2,1,1,2,1,1,2,1', 
dish_odi='1,2,3,1,2,3,1,2,3', 
do_odi='1,1,1,1,1,1,1,1,1' 
dish_option_id='2'

    What I want is all order_data, dish_id + amounts + ordered_dish_id, dish_option_id + ordered_dish_id. I want the ordered_dish_ids from the last two tables to link the dish_option to the corresponding dish.

    Ike Walker
    • 64,401
    • 14
    • 110
    • 109
    Zerre
    • 105
    • 2
    • 2
    • 7
    • 1
      One observation: order_id is redundant in ordered_dish_options – Pete Feb 09 '17 at 15:57
    • No, because every for every order the ordered_dish_id (odi) starts incrementing from 1. So order1 has 3 dishes with odi1,2,3. order2 has for example 4 dishes with odi1,2,3,4. So when ordered_dish_options is joined it must be joined on order_id AND linked to corresponding odi from ordered_dish – Zerre Feb 09 '17 at 19:57
    • Have it your own way – Pete Feb 24 '17 at 11:17

    1 Answers1

    0

    I finally got a query with the expected result: SELECT orders.*, ordered_dishes.dishids,ordered_dishes.amounts,ordered_dish_options.odos,ordered_dish_options.dois FROM orders LEFT JOIN (SELECT order_id, GROUP_CONCAT(ordered_dishes.dish_id) dishids, GROUP_CONCAT(ordered_dishes.amount) amounts from ordered_dishes GROUP BY order_id) ordered_dishes ON orders.order_id=ordered_dishes.order_id LEFT JOIN (SELECT order_id, GROUP_CONCAT(ordered_dish_options.ordered_dish_id) odos, GROUP_CONCAT(ordered_dish_options.dish_option_id) dois FROM ordered_dish_options GROUP BY order_id) ordered_dish_options ON orders.order_id=ordered_dish_options.order_id WHERE orders.order_id=6

    the so editor is a little crappy, i couldnt get the query to code format with 4 spaces.

    Zerre
    • 105
    • 2
    • 2
    • 7