Problem with join and subqueries

Question

Problem with join and subqueries

Navigation

#1 by (1 votes)

1

I have an exercise to answer, however I do not get the complete solution, only partial ideas, the DBMS used is SQL Server.

Considering the Customers, Orders, and Order Details tables, create an SQL query that brings the order ID, the order date, the name of the company that placed the order, and the total value of the order.

/****** [Customers]    ******/
CREATE TABLE [dbo].[Customers](
    [CustomerID] [nchar](5) NOT NULL,
    [CompanyName] [nvarchar](40) NOT NULL,
    [ContactName] [nvarchar](30) NULL,
    [ContactTitle] [nvarchar](30) NULL,
    [Address] [nvarchar](60) NULL,
    [City] [nvarchar](15) NULL,
    [Region] [nvarchar](15) NULL,
    [PostalCode] [nvarchar](10) NULL,
    [Country] [nvarchar](15) NULL,
    [Phone] [nvarchar](24) NULL,
    [Fax] [nvarchar](24) NULL
);

/****** [Orders]    ******/
CREATE TABLE [dbo].[Orders](
    [OrderID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerID] [nchar](5) NULL,
    [EmployeeID] [int] NULL,
    [OrderDate] [datetime] NULL,
    [RequiredDate] [datetime] NULL,
    [ShippedDate] [datetime] NULL,
    [ShipVia] [int] NULL,
    [Freight] [money] NULL,
    [ShipName] [nvarchar](40) NULL,
    [ShipAddress] [nvarchar](60) NULL,
    [ShipCity] [nvarchar](15) NULL,
    [ShipRegion] [nvarchar](15) NULL,
    [ShipPostalCode] [nvarchar](10) NULL,
    [ShipCountry] [nvarchar](15) NULL
);

/****** [Orders Details]    ******/
CREATE TABLE [dbo].[Order Details](
    [OrderID] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL 
);

What's wrong with this code, I can not find the solution:

SELECT Orders.OrderID, OrderDate, CompanyName, 
       (SELECT OrderID,   SUM([UnitPrice]) 
         FROM [Order Details] 
         GROUP BY OrderID 
         HAVING COUNT(OrderID)=1) AS TOTAL
 FROM Orders
     INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID
     INNER JOIN [Order Details] ON [Order Details].OrderID = Orders.OrderID;

sql sql-server

asked by anonymous 19.09.2018 / 01:28

1 answer

How to generate grafana container correctly? How to open a new window only if it is not already open?

score 1 · Answer 1

There are a few ways to get the result. Considering the code you posted, it seems to me that just reviewing the calculation of the order total.

The subquery

  SELECT OrderID,   SUM([UnitPrice]) 
     FROM [Order Details] 
     GROUP BY OrderID 
     HAVING COUNT(OrderID)=1

must be transformed into a correlated sub-set

  SELECT sum (OD.UnitPrice) 
     from [Order Details] as OD
     where OD.OrderID = Orders.OrderID

The complete code looks like this:

SELECT Orders.OrderID, Orders.OrderDate, Customers.CompanyName, 
      (SELECT sum (OD.UnitPrice) 
         from [Order Details] as OD
         where OD.OrderID = Orders.OrderID) as TOTAL
 from Orders
     inner join Customers on Customers.CustomerID = Orders.CustomerID;