CODEWITHSUNDEEP
sqlpostgresql
RyanH
RyanH

Reputation: 79

SQL where clause returning nothing

In the query below I am trying to make a quick summary table to display some information. Using the where clause it is meant to find the information relevant to that publisher from database. After looking through the other definitions for an hour or so I can't see where I am going wrong.

Sorry to be a pain and this will probably be something so simple but is there anything obviously wrong with the query I have below. 

    SELECT Publisher.Name AS "Publisher Name", 
       Book.Title AS "Book Title", 
       ShopOrder.OrderDate AS "Order Date", 
       OrderLine.Quantity AS "Order Quantity", 
       (SUM(Quantity*UnitSellingPrice)) AS "Total Value"
   FROM ShopOrder
   INNER JOIN OrderLine
   ON ShopOrder.ShopOrderID=OrderLine.ShopOrderID
   INNER JOIN Book
   ON OrderLine.BookID=Book.BookID
   INNER JOIN Publisher
   ON Publisher.PublisherID=Book.PublisherID
   WHERE Publisher.Name='Oxford'
   GROUP BY ShopOrder.ShopOrderID, 
            Publisher.PublisherID, 
            Book.BookID, 
            OrderLine.Quantity
   ORDER BY ShopOrder.OrderDate;

Thanks so much to anybody who can help!

EDIT: Relevant table defs

CREATE TABLE Publisher
(
    PublisherID INTEGER,
    Name VARCHAR(50),

    PRIMARY KEY(PublisherID)
);

CREATE TABLE Book
(
    BookID INTEGER,
    Title VARCHAR(50),
    Price DECIMAL (10, 2),
    CategoryID INTEGER REFERENCES Category(CategoryID),
    PublisherID INTEGER REFERENCES Publisher(PublisherID),

    PRIMARY KEY(BookID)

);

CREATE TABLE ShopOrder
(
    ShopOrderID INTEGER,
    OrderDate DATE,
    ShopID INTEGER REFERENCES Shop(ShopID),
    SalesRepID INTEGER REFERENCES SalesRep(SalesRepID),

    PRIMARY KEY(ShopOrderID)
);

CREATE TABLE OrderLine
(
    ShopOrderID INTEGER REFERENCES ShopOrder(ShopOrderID) ON DELETE CASCADE,
    BookID INTEGER REFERENCES Book(BookID),
    Quantity INTEGER,
    UnitSellingPrice DECIMAL (10,2)
);

Upvotes: 0

Views: 111

Answers (2)

RyanH
RyanH

Reputation: 79

Stanleys answer was what really helped in this situation but for anybody somehow in the future looking for the answer to this the correct query should have been as follows.

SELECT Publisher.Name AS "Publisher Name", Book.Title AS "Book Title", ShopOrder.OrderDate AS "Order Date", 
OrderLine.Quantity AS "Order Quantity", (SUM(Quantity*UnitSellingPrice)) AS "Total Value"
FROM ShopOrder
INNER JOIN OrderLine
ON ShopOrder.ShopOrderID=OrderLine.ShopOrderID
INNER JOIN Book
ON OrderLine.BookID=Book.BookID
INNER JOIN Publisher
ON Publisher.PublisherID=Book.BookID
WHERE Publisher.Name='Oxford'
GROUP BY publisher.name, book.title, shoporder.orderdate, orderline.quantity
ORDER BY ShopOrder.OrderDate;

Upvotes: 0

D Stanley
D Stanley

Reputation: 152491

This is not a definite answer but I've added it to show how I would debug the query :

Start with Publisher:

SELECT Publisher.Name AS "Publisher Name"
FROM Publisher
WHERE Publisher.Name='Oxford'

Check results...

Add Book

SELECT Publisher.Name AS "Publisher Name", 
   Book.Title AS "Book Title"
FROM Book
INNER JOIN Publisher
ON Publisher.PublisherID=Book.PublisherID
WHERE Publisher.Name='Oxford'

Check results...

Add OrderLine:

SELECT Publisher.Name AS "Publisher Name", 
   Book.Title AS "Book Title", 
   OrderLine.Quantity AS "Order Quantity"
FROM  OrderLine
INNER JOIN Book
  ON OrderLine.BookID=Book.BookID
INNER JOIN Publisher
  ON Publisher.PublisherID=Book.PublisherID
WHERE Publisher.Name='Oxford'

Check results...

Add ShopOrder

SELECT Publisher.Name AS "Publisher Name", 
   Book.Title AS "Book Title", 
   ShopOrder.OrderDate AS "Order Date", 
   OrderLine.Quantity AS "Order Quantity"
FROM  ShopOrder
INNER JOIN OrderLine
  ON ShopOrder.ShopOrderID=OrderLine.ShopOrderID
INNER JOIN Book
  ON OrderLine.BookID=Book.BookID
INNER JOIN Publisher
  ON Publisher.PublisherID=Book.PublisherID
WHERE Publisher.Name='Oxford'

Check results...

Add the SUM and GROUPING:

 SELECT Publisher.Name AS "Publisher Name", 
   Book.Title AS "Book Title", 
   ShopOrder.OrderDate AS "Order Date", 
   OrderLine.Quantity AS "Order Quantity", 
   (SUM(Quantity*UnitSellingPrice)) AS "Total Value"
FROM  ShopOrder
INNER JOIN OrderLine
  ON ShopOrder.ShopOrderID=OrderLine.ShopOrderID
INNER JOIN Book
  ON OrderLine.BookID=Book.BookID
INNER JOIN Publisher
  ON Publisher.PublisherID=Book.PublisherID
WHERE Publisher.Name='Oxford'
GROUP BY ShopOrder.ShopOrderID, 
         Publisher.PublisherID, 
         Book.BookID, 
         OrderLine.Quantity
ORDER BY ShopOrder.OrderDate;

Upvotes: 1

Related Questions