Complicated SQL Query 1

[anuktac]

Hi,
I have two tables SITE_MASTER and MODIFIED_SITES.
The structures are as follows:
SITE_MASTER
---------------------
site_no varchar2(8) primary key,
adress varchar2(50)

same for MODIFIED_SITES.

My query should be like this:
select site_no,address from SITE_MASTER if there is no matching site_no in MODIFIED_SITES, but if a site exists in MODIFIED_SITES, then pick up the site_no and address from MODIFIED_SITES.

example:
say, the data in SITE_MASTER is like:

site_no Address
------- -------
123 50,Penarth Road
321 96, Taff Embankment
xxx Lords Pavilion
yyy 86, Queen's Street
abc 1, Turk Road

data in MODIFIED_SITES is like:
site_no Address
------- -------
xxx 23, Midland Avenue
abc 321, Lost Knife Circle

When I run my query, it should return:
site_no Address
------ -------
123 50,Penarth Road
321 96, Taff Embankment
xxx 23, Midland Avenue
yyy 86, Queen's Street
abc 321, Lost Knife Circle

(As you see, for xxx and abc the address has been picked up from MODIFIED_SITES instead of SITE_MASTER, since a match was found).
Can anyone help me to write the query?
-Anukta

 

[sem]

select a.site_no, nvl(b.address, a.address)
from SITE_MASTER a, MODIFIED_SITES b
where a.site_no = b.site_no(+)


Regards, Dima

 

[anuktac]

Thanks it was very simple, wasn't it? I had just worked it out too....