Fetch data from multiple tables

Question

I have three primary tables:

1. Customer
2. Product
3. Order

Relations:

Many to many between product and order.

CREATE TABLE Customer (
 CustomerId int NOT NULL AUTO_INCREMENT,
 CustomerName varchar(50) NOT NULL,
 CustomerMail varchar(50) NOT NULL,
 CONSTRAINT Customer_pk PRIMARY KEY (CustomerId)
);
-- Table `Order`
CREATE TABLE `Order` (
 OrderId int NOT NULL AUTO_INCREMENT,
 OrderDate date NOT NULL,
 CustomerId int NOT NULL,
 CONSTRAINT Order_pk PRIMARY KEY (OrderId)
);
-- Table Product
CREATE TABLE Product (
 ProductId int NOT NULL AUTO_INCREMENT,
 ProductName varchar(50) NOT NULL,
 SKU varchar(50) NOT NULL,
 Price decimal(8,2) NOT NULL,
 CONSTRAINT Product_pk PRIMARY KEY (ProductId)
);
-- Table ProductOrder
CREATE TABLE ProductOrder (
 ProductOrderId int NOT NULL AUTO_INCREMENT,
 OrderId int NOT NULL,
 ProductId int NOT NULL,
 Quantity int NOT NULL,
 CONSTRAINT ProductOrder_pk PRIMARY KEY (ProductOrderId)
);
ALTER TABLE `Order` ADD CONSTRAINT Order_Customer FOREIGN KEY Order_Customer (CustomerId)
 REFERENCES Customer (CustomerId);
-- Reference: ProductOrder_Order (table: ProductOrder)
ALTER TABLE ProductOrder ADD CONSTRAINT ProductOrder_Order FOREIGN KEY ProductOrder_Order (OrderId)
 REFERENCES `Order` (OrderId);
-- Reference: ProductOrder_Product (table: ProductOrder)
ALTER TABLE ProductOrder ADD CONSTRAINT ProductOrder_Product FOREIGN KEY ProductOrder_Product (ProductId)
 REFERENCES Product (ProductId);

I need to write a query for:

 Create a query for getting the total quantity of products
 ordered and the total value for all orders made
 on the current date.

This is my solution:

-- query for current date
SELECT SUM(quantity) AS TotalQuantity, SUM(quantity * price) AS TotalSaleAmount 
 FROM productorder, product, `order`
 WHERE productorder.ProductId = product.ProductId 
 AND 
 `order`.OrderId = productorder.OrderId 
 AND 
 `order`.OrderDate = CURDATE()

Can someone help me in optimizing it?

• Mat: "Have I written the correct query"? Did you test it out? Did you get the results you expected?
• Unbreakable: I am getting the output but I am not sure if this is correct. I saw one or two solution using joins. So I am confused that whether the query I have written is correct or not

Answer 1

I am getting the output but I am not sure if this is correct. I saw one or two solution using joins So I am confused that whether the query I have written is correct or not

It is a good practice to use JOIN : Explicit vs implicit SQL joins.

Second you could use aliases so you do not have to type entire table name.

SELECT SUM(po.quantity) AS TotalQuantity
 ,SUM(po.quantity*p.price) AS TotalSaleAmount 
FROM productorder po
JOIN product p
 ON po.ProductId = p.ProductId 
JOIN `order` o
 ON o.OrderId = po.OrderId 
WHERE o.OrderDate = CURDATE();

SqlFiddleDemo