Table relations and mysql select statement

[bambinou1980]

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

 

 

 

[maxxd]

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.

[Barand]

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.

[maxxd]

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?