How can I change this sql statement into something Crystal Reports

Question

I have three tables in my Crystal Reports and it looks like this:

Property
  ID
  Other Data
Unit
  ID
  PropertyID <-- fk to Property
  Other Data
Tenant
  ID
  UnitID <-- fk to Unit
  Other Data

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:

SELECT       Unit.ID
FROM         Unit
WHERE NOT EXISTS (SELECT 1 FROM Tenant WHERE Unit.ID = UnitID)

Now, I know I have to group by Property --> Units 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.

How can this be done?

Answer 1

select unit.id 
from unit
       left outer join 
     tenant 
       on unit.id = tenant.unitid
where tenant.unitid is null

If I recall the crystal query builder, you can join tables with a left join or a "not equal" join. If you choose the left join, also choose criteria that only includes rows where the joined table is null (thus no relationship between those keys)

Answer 2

if you have the query (based on your comment to @Joe Koberg), just put it in a stored procedure and have crystal run that.