SQL join to find relavant codes and address

Below table contain address information.

Code	Code1	Code2	Address
DAN001	DAN004	DAN005	ABC, Newyork.
DAN004	DAN004	DAN004	Delhi, India
DAN005	DAN005	DAN005	Ever, Belgium

Please find table details below:

Each Code have associated code1 and code2 and address last column. for example:

• DAN001 is associated with DAN004 and DAN005.
• Address for DAN001 is ABC, Newyork.
• Address for DAN004 is Delhi,India.
• Address for DAN005 is Ever, Belgium.

I am looking for the single SQL query, which will give me associated code1 and code2 and corresponding addresses for each codes in the single query for the given code.

I made below query but this is not working as excepted:

 select a.code, b.code1, b.address code1_address , c.code2, c.address code2_address
 from address a,address b, address c  where a.code='DAN001'
 and a.code1= b.code and a.code2=c.code;

I am using Oracle10g.

Excepted result:

DAN004, ABC Newyork, DAN005, EVER Belgium

Answer

Are you just looking for joins?

select a.*, a1.address, a2.address
from address a left join
     address a1
     on a1.code = a.code1 left join
     address a2
     on a2.code = a.code2;

This uses left join in case any of the code columns are empty.