Jump to content

Table relations and mysql select statement


bambinou1980

Recommended Posts

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
 
 
 
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.