Hello everyone,

I have created a page that displays the orders that every customer has done in an e-commerce site.When the user order some products then it is stored in two tables the information. The first table is the Orders which stores the orderID(auto increment,primary key)the customerID, the orderdate and the amount.When the orderID is been created then the details for the products that the user bought are stored in another table called order_items which has information for the specific items the user bought.This table takes
the orderID from the table Orders and displayes the item that the user bought.
For example if the customer has an order with reference 001 and has bought 2 items then in the order_items table it will show the

orderID:001,itemID:01
orderID:001,itemID:02

The problem I have is that I have created an administrator panel that the user can see all the orders. THe page that displays the info for the orders includes the following fileds:
Order Date, Total amount, Quantity, Customer, Stock number, Order ID, Item ID

In order to display the above fields I have created a query which combines 4 tables( Orders ,Order_items, Customers,Items,Categories)as they are connected between them.I used the following sql statement:

select Orders.order_date, Orders.total_amount,Order_items.quantity, Customers.user, Items.ref, Orders.orderID, Order_items.itemID
 from Orders ,Order_items, Customers,Items,Categories
where Orders.orderID=Order_items.orderID
and Customers.customerID=Orders.customerID
and Items.itemID=Order_items.itemID

When I run this query it display many records by dublicating the results.SO if I want to see an order that has two items it will display 15 times rather than 2 times. I am using Mysql as a database
I think I have to use inner or outer join in order to display the correct results.Before I am not very familiar with the joins I would appreciate your help.

Thanks,
Xenia

that's 5 tables although categories isn't actually referenced in the query.

yep your query does a many to many join 3 INNER (or LEFT) JOINs should do.

order of the joins looks to be

inner join customers to orders, inner join orderItems to orders, inner join items to order_items