SQL query problem

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

This is a repost of your question a few hours ago... A bit unpatient..?

And to provide you with some info.. LEFT JOIN on your address table. That should do the trick.

The link to your post...

Answer 2

what you are looking for is an outer join (other can correct my syntax)

Select * from emp Right outer join address on emp.empno = address.empno

Any row that has A NULL in the fields for the Address table is a row where no address exists.

Answer 3

SELECT emp.empno, emp.ename, IF(address.empno IS NULL, 'no address', 'address found') 
FROM emp LEFT JOIN address ON emp.empno=address.empno

Answer 4

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

Though I have answered this question here