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?

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