Have Geek Brain

How can I change this sql statement to something Crystal Reports

I have three tables in 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 will show only the Units (grouped by the property they belong to) that are not associated with it. Some nice people helped me figure out the SQL for the query, and like this:

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

Now I know what I need to group Property --> Units

, but that is as far as I know. It turns out that the fields of the SQL expression only allow a single result to be returned, not a result, so the search is done even though the result from the above query cannot be done that way, and I can't just type in plain sql.

How can I do that?

0


a source to share


2 answers


if you have a request (based on your comment on @Joe Koberg) just put it in a stored procedure and try the crystal version.



+1


a source 


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


If I remember building a crystal query, you can join tables with a left join or an "unequal" join. If you select the left join, also select criteria that only includes rows where the joined table is zero (so there is no relationship between these keys)

0


a source






More articles:


All Articles