SQL in oracle

Hello Experts,

I have two tables as like below:

desc Sample1
Name          Null Type          
------------- ---- ------------- 
FACILITY_ID        NUMBER        
FACILITY_NAME      VARCHAR2(100) 

desc Sample2
Name          Null Type               
------------- ---- ------------------ 
FACILITY_ID        NUMBER             
FACILITY_NAME      VARCHAR2(100 CHAR) 

Open in new window



And data as like below:

select * from Sample1 ;


FACILITY_ID, FACILITY_NAME 
--------------------------
101	Sharath
102	Swadhin
103	Keron
104	NAGU
105	Siva




select * from sample2;

FACILITY_ID, FACILITY_NAME 
--------------------------
101	Sharath

Open in new window



I want the result to be if Records are There in Both Sample1 and Sample2 Then Get From Sample 2
If records doesnt Exist in Sample2 and exist only in Sample 1 Then Get From Sample1.
LVL 17
Swadhin RaySenior Technical Engineer Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JavierVeraCommented:
You could create a mid table wich you populate initially with the sample2 and then when getting from sample1 just validating if the record already exists there. If not, then insert. There you have sample2 and sample1.
0
PortletPaulfreelancerCommented:
That sounds like a LEFT OUTER JOIN I think, like this
select
          coalesce(sample2.FACILITY_ID,sample1.FACILITY_ID) as FACILITY_ID
        , coalesce(sample2.FACILITY_NAME, sample1.FACILITY_NAME) as FACILITY_NAME
from sample1
LEFT OUTER JOIN sample2 ON sample1.FACILITY_ID = sample2.FACILITY_ID

Open in new window

really it does not matter which table facility_id comes from (because they would be equal if in both tables), but coalesce will do what you have asked for.

nb: If there are records in sample2 that do not exist in sample1 a different approach would be needed
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
select FACILITY_ID, FACILITY_NAME from sample2
UNION
select FACILITY_ID, FACILITY_NAME from sample1

would be another way,
and if there are records unique to either table then all would be listed by this approach
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.