title: Joining two tables using a Subquery in the FROM Clause
body: <p>I'm losing my mind on this question I have on my homework.
  NO I DON'T WANT YOU TO GIVE ME THE ANSWER! I just want to be pushed into the right direction, because I've been struggling with this.</p>
  
  <p>Here is the question.</p>
  
  <blockquote>
    <p>Write a SELECT statement that returns three columns: EmailAddress, OrderID, and the order total for each customer. To do this, you can group the result set by the EmailAddress and OrderID columns. In addition, you must calculate the order total from the columns in the OrderItems table.</p>
    
    <p>Write a second SELECT statement that uses the first SELECT statement in its FROM clause. The main query should return two columns: the customer’s email address and the largest order for that customer. To do this, you can group the result set by the EmailAddress column.</p>
  </blockquote>
  
  <p>This is what I have so far:</p>
  
  <pre><code>SELECT EmailAddress, OrderID, SUM(ItemTotal * Quantity) AS OrderTotal
  FROM Customers JOIN
      (SELECT OrderID, MAX(ItemTotal * Quantity) AS LargestOrder
       FROM Orders, OrderItems
       WHERE OrderItems.OrderID = Orders.OrderID)
  GROUP BY EmailAddress;
  </code></pre>
  
  <p>The way the tables are set up is:</p>
  
  <pre><code>Customers Table:
  CustomerID, EmailAddress, Password, FirstName, LastName, ShippingAddressID, BillingAddressID
  
  OrderItems Table:
  ItemID, OrderID, ProductID, ItemPrice, DiscountAmount, DiscountTotal, PriceTotal, ItemTotal, Quantity
  
  Order Table:
  OrderID, CustomerID, OrderDate, ShipAmount, TaxAmount, ShipDate, ShipAddressID, CardType, CardNumber,CardExpires, BillingAddressID.
  </code></pre>
  
  <p>Any help would be greatly appreciated!</p>