Outer join with multiple conditions

[meghaAgrawal]

I have unit and tenant tables. One unit can have 0 or 1 tenant. I have to extract all units with either no tenants or with tenants of particular status codes like 'Past' and 'Future'. I cannot apply OR operator on tenant.status as tenant is optional table for join.

Following query doesn't bring correct data. And I need to write something like this. What is the alternative? I am on Oracle 8i.

select count(u.code)
from
unit u, tenant t
where t.hunit (+) = u.hmy
and (t.status (+) = 'Past' or t.status (+) = 'Future')
and nvl(u.excluded, 0) = 0

Thanks

-megha

 

[meghaAgrawal]

I think I figured it out:

select count(u.scode)
from unit u,
(select * from tenant where status in ('Past', 'Future')) t
where u.hmy = t.hunit (+) and nvl(u.exclude, 0) = 0