title: How to compare row with subquery output in SQL?
body: <p>There are two tables, OrderID and Order Details.</p>
  
  <p>Orders:</p>
  
  <pre><code>OrderID PK
  Freight
  |....|
  </code></pre>
  
  <p>Order Details:</p>
  
  <pre><code>OrderID PK FK
  ProductID PK FK
  UnitPrice
  Quantity
  |....|
  </code></pre>
  
  <p>Each <code>OrderID</code> is unique for <code>Orders</code>, but Order Details can contain several details for the same <code>OrderID</code> and different <code>ProductId</code>, <code>UnitPrice</code> etc. </p>
  
  <p>So, in Order Details we can see two, three or more orders (<code>OrderID</code>).</p>
  
  <p>My task is to select physical addresses of all Freight records which more than total cost of entire order Freight > UnitPrice * Quantity * (Quantity of OrderID in Order Details)</p>
  
  <pre><code>SELECT %%physloc%%
  FROM Orders
  WHERE Freight &gt; (SELECT SUM(UnitPrice * Quantity)
                   FROM [Order Details]
                   GROUP BY OrderID);
  </code></pre>
  
  <p>And of course I've got</p>
  
  <blockquote>
    <p>'Subquery returned more than 1 value...'</p>
  </blockquote>
  
  <p>I try to use Top but in that case I have a wrong selection.</p>
  
  <p>All that I need is to compare somehow each Freight with OrderID records with each records from that subquery with same OrderID. But I have no idea how. </p>
  
  <p>Maybe someone can find a different way, It would be great. </p>
  
  <p>I use SQL Server 2008</p>
  
  <p>Thank you all.</p>