Correlated subquery in sql to solve below question

Question

I got an assignment, I could not fix it, can anybody help me:

Use a correlated subquery to return one row per customer, representing the customer’s most current order (the one with the latest date) with amount of sales tax greater than 100 dollars. Each row should include these four columns:EmailAddress, OrderID, OrderDate, and TaxAmount.

SELECT c.EmailAddress, o.TaxAmount, o.OrderDate, o.OrderID, o.CustomerID
FROM Customers AS c, Orders AS o
WHERE o.TaxAmount >100

Customers Table:

 [CustomerID]
  ,[EmailAddress]
  ,[Password]
  ,[FirstName]
  ,[LastName]
  ,[ShippingAddressID]
  ,[BillingAddressID]

and Orders table has the following columns:

 [OrderID]
  ,[CustomerID]
  ,[OrderDate]
  ,[ShipAmount]
  ,[TaxAmount]
  ,[ShipDate]
  ,[ShipAddressID]
  ,[CardType]
  ,[CardNumber]
  ,[CardExpires]
  ,[BillingAddressID]

Tip: the correlated subquery would find `MAX(OrderDate)` in all rows `WHERE CustomerID = c.CustomerID` — Alexey S. Larionov, Aug 03 '20 at 22:01

score 0 · Accepted Answer · answered Aug 03 '20 at 22:04

0

select EmailAddress, OrderID, OrderDate, TaxAmount
from Customers cus
 inner join Orders ord
  on ord.customerid = cus.customerid
where ord.orderdate = (select max(orderdate) 
                       from orders ord_
                       where ord_.customerid = ord.customerid
                        and ord_.taxamount > 100)

answered Aug 03 '20 at 22:04

Lars Skaug

1,376
1
7
13

The inner query refers to the external one in the where clause (`where ord_.customerid = ord.customerid`), which is not possible In SQL. For instance, see this [other question](https://stackoverflow.com/questions/2645485/referencing-outer-querys-tables-in-a-subquery). – Roberto Trani Aug 04 '20 at 05:15
That is how correlated sub queries work. https://en.wikipedia.org/wiki/Correlated_subquery# – Lars Skaug Aug 04 '20 at 19:12

Correlated subquery in sql to solve below question

1 Answers1