bambinou1980 Posted October 1, 2015 Share Posted October 1, 2015 Hello, I have a question on the SELECT statements when you have 4 tables interconnected in this way: order <----where the data is the most important --id --grand_total --cust_name --cust_surname --cust_address order_items <--table --id --order_id --items_id --name --quantity --unit_price --total items <--table --id --name --price --description items_toppings<--table --id --order_id --order_items_id --items_id --topping_name --topping_price As you can see there are 4 tables which are related to each others. The most important table is the "order", the others should be pulled only if there is data linked. I would like to know how could I query the order table while pulling the order_items, items and items_toppings at the same time, would this works please? $sql = "SELECT o.id, o.grand_total, o.cust_name, o.cust_surname, o.cust_address, o_i.name, o_i.quantity, o_i.unit_price, o_i.total, i.name, i.price, i.description, i_t.topping_name, i_t,topping_price FROM order AS o LEFT JOIN order_items as o_i items as i items_toppings as i_t ON o.id = o_i.order_id o_i.items_id = i.id ORDER BY o.id DESC $limit"; Thank you for your help! Ben Quote Link to comment Share on other sites More sharing options...
maxxd Posted October 1, 2015 Share Posted October 1, 2015 This is untested, but you should get the idea. Basically, you have to join each table individually on the foreign keys. $sql = " SELECT o.id, o.grand_total, o.cust_name, o.cust_surname, o.cust_address, o_i.name, o_i.quantity, o_i.unit_price, o_i.total, i.name, i.price, i.description, i_t.topping_name, i_t,topping_price FROM order AS o LEFT JOIN order_items as o_i ON o.id = o_i.order_id LEFT JOIN items as i ON o_i.items_id = i.id LEFT JOIN items_toppings as i_t ON i_t.items_id = i.id ORDER BY o.id DESC $limit "; I don't think it's necessary to include the order id or order items id in your toppings table, because the toppings aren't related to the order, but the item. The item is related to the order through order_items, so you've got your through-line there. Quote Link to comment Share on other sites More sharing options...
Barand Posted October 1, 2015 Share Posted October 1, 2015 I don't think it's necessary to include the order id or order items id in your toppings table, because the toppings aren't related to the order, but the item. The item is related to the order through order_items, so you've got your through-line there. I would agree that if you have the order_item_id then you do not need order id. However, in the case of toppings it could be that item X has a choice of toppings and you want the choice applicable to the particular ordered item. Though I am guessing here about the actual relationship, without knowing the full schema. Quote Link to comment Share on other sites More sharing options...
maxxd Posted October 2, 2015 Share Posted October 2, 2015 I would agree that if you have the order_item_id then you do not need order id. However, in the case of toppings it could be that item X has a choice of toppings and you want the choice applicable to the particular ordered item. Though I am guessing here about the actual relationship, without knowing the full schema. Good point. Even then, wouldn't you need an intermediary table for multiple toppings per item, which would negate the need to have the field in the toppings table, right? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.