title: How can I change this sql statement into something Crystal Reports
body: <p>I have three tables in my Crystal Reports and it looks like this:</p>
  
  <pre><code>Property
    ID
    Other Data
  Unit
    ID
    PropertyID &lt;-- fk to Property
    Other Data
  Tenant
    ID
    UnitID &lt;-- fk to Unit
    Other Data
  </code></pre>
  
  <p>The report is going to show only Unit's (grouped by the property they belong to) that do not have any tenant's linked to it. Some nice people have helped me figure out the SQL for the query and it's like this:</p>
  
  <pre><code>SELECT       Unit.ID
  FROM         Unit
  WHERE NOT EXISTS (SELECT 1 FROM Tenant WHERE Unit.ID = UnitID)
  </code></pre>
  
  <p>Now, I know I have to group by <code>Property --&gt; Units</code> but that's about as far as I know. It turns out the SQL Expression Fields only allow for the return of a single result and not a result set so searching though the result set from the above query can't be done that way, and I can't just enter plain sql.</p>
  
  <p>How can this be done?</p>