how to work with Derived Tables

Question

I am trying to understand a derived tables I kinda have an idea but still messing with it. I did this code that I dont think is right. I don't know if I have something input wrong or left something out. The table I am working with is a distinct company name from the customers table where the orders have a higher discount than.2.I have been looking over it maybe I have some of the names backwards or something.

SELECT DISTINCT c.CompanyName
From Customers As c
Join
(Select OrderID
From Orders as o
Join [Order Details] as od
ON c.Customers = od.OrderID
Where od.OrderID = '<.2'

The last Part is not working so well with the.2

How the columns are set up in the northwind database you click on tables and go down to see the dbo.Order Details and click on columns you will find OrderId, ProductID, UnitPrice, Quantity, Disounts. Then you will have the Custumers table which had CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, COuntry, Phone, and Fax. Now if you go under the order table you will have OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate,ShipVia, Frieght, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry.

I doubt the discount value is stored in the od.OrderID column. Is there another column for that value? — Joe Stefanelli
sql dialects and features vary significantly from one vendor to another. You should update your question to indicate which one you are working with. — gview
See if you go down to dbo.Order Detail and look under it you will see the discount — st1984

Joe Stefanelli Joe Stefanelli · Accepted Answer · 2011-10-11T20:06:13

Something like this should do it.

SELECT c.CompanyName
    FROM Customers c
    WHERE EXISTS(SELECT NULL
                     FROM Orders o
                         INNER JOIN [Order Details] od
                             ON o.OrderID = od.OrderID
                                 AND od.Discount > .2
                     WHERE o.CustomerID = c.CustomerID)

how to work with Derived Tables

1 Answers