title: Subquery between three tables using Northwind database with SQL Server
body: <ol>
  <li>Table <code>[Orders] : OrderID(Primary Key), CustomerID</code></li>
  <li>Table <code>[Order Details] : OrderID(Primary Key), ProductID(Primary Key), Discount</code></li>
  <li>Table <code>[Customers] : CustomerID[Primary Key]</code></li>
  </ol>
  
  <p>With these three tables, I want to query <code>productID</code> with highest discount for each <code>CustomerID</code>. I need column for <code>ProductID</code>, <code>CustomerID</code> and <code>Discount</code>. How can I solve this problem? All kinds of helps are really appreciated.</p>
  
  <p>Following script I have tried :</p>
  
  <pre><code>select ProductID, a.customerID, 
      (select MAX(discount) 
       from [Order Details] 
       where a.CustomerID=c.customerID
      )
  from Orders a 
      join [Order Details]
          on a.OrderID=[Order Details].OrderID 
      join Customers c
          on a.CustomerID=c.CustomerID    
  order by customerID
  </code></pre>