CODEWITHSUNDEEP
phpmysql
Faizuddin Mohammed
Faizuddin Mohammed

Reputation: 4328

How to retrieve all the data stored in different tables in a MySQL database?

I have four tables in my MySQL Database.

orders table

order_id | customer_id

items Table

item_id | item_name | item_price | item_quantity

orders_items table

order_item_id | order_id | item_id

customers table

customer_id | customer_name

I want to display a table that lists all the orders from the order table with all the remaining data joined together.

Like this:

order_id | item_id | item_name | item_quantity | customer_id | customer_name | price

I use MySQL and PHP.

Upvotes: 0

Views: 80

Answers (2)

Muhammad Bilal
Muhammad Bilal

Reputation: 2134

Use something like this:

`SELECT * FROM orders 
LEFT JOIN order_items ON order_items.order_id=orders.order_id 
LEFT JOIN items ON items.item_id=orders_items.item_id 
LEFT JOIN customers ON customers.customer_id=orders.customer_id`

Upvotes: 0

Jens
Jens

Reputation: 69440

You have to learn aboud joins:

select orders.order_id , items.item_id , item_name , item_quantity , customers.customer_id , customer_name , price 
from `orders` 
join `orders_items` on  orders.order_id = orders_items.order_id 
join `items` on  items.item_id = orders_items.item_id 
join `customers` on orders.customer_id = customers.customer_id

Upvotes: 0

Related Questions