very simple SQL query in oracle 9

Question

I have two tables:

emp (empno,ename)
address(empno,address)

emp can have 0 or more address.

I want to print a result as follows:

• when emp has 0 address then: eno,ename,"no address"
• when emp has 1 or more address then: enomename ,"address found"

I am using oracle 9db

Answer 1

select e.empno,e.ename, 'Address Found' as Status from
emp e, address a
where e.empno=a.empno
and a.address is not null
UNION ALL
select e.empno,e.ename, 'No Address' as Status from
emp e, address a
where e.empno=a.empno
and a.address is null

Answer 2

Try this

Inputs:

Address Table

empno   address
1   address1 for name1
1   address2 for name1
2   address1 for name2
2   address2 for name2
2   address3 for name2

Emloyee Table

empno   ename
1   name1
2   name2
3   name3
4   name4
5   name5

Query

select distinct(e.empno),e.ename,
    case when
    a.address IS null then 'No Address' else 'Address found' end as Status
    from @emp e
    left join @address a
    on e.empno  = a.empno 

Output:

empno   ename      Status
1        name1  Address found
2        name2  Address found
3        name3  No Address
4        name4  No Address
5        name5  No Address

Answer 3

select empno, ename, 
    case when (select count(1) from address where empno=emp.empno)>0 
         then 'adress found'
         else 'no address'
    end
  from emp