CODEWITHSUNDEEP
sqloracle-database
praveen_r
praveen_r

Reputation: 897

Joining 2 tables ON a column with some missing entries

Consider these 2 tables:

CustAddress

Name             Address
----             -------
John             116 States Ave
Mike             404 16th Street

CustPhone

Name             Phone
----             -------
John             342-345-456
Smith             678-435-567

How do I combine these into a single result like this in Oracle:

Customer info with Address and Phone:

Name             Phone               Address
----             -------             -------
John             342-345-456         116 States Ave
Smith            678-435-567
Mike                                 404 16th Street

Full Outer Join doesn't help as I end up losing some data.

Here is a SQL Fiddle to play with: http://sqlfiddle.com/#!9/ebb9b/1

Upvotes: 1

Views: 50

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Using a full join.

select coalesce(ca.name,cp.name) as name, cp.phone, ca.address
from custaddress ca
full join custphone cp on ca.name=cp.name

Or using union all assuming there is at most one record per name in either of the tables.

select name,max(phone) as phone,max(address) as address
from (select name,phone,null as address from custphone
      union all
      select name,null,address from custaddress
     ) x
group by name

Upvotes: 3

Paul Abbott
Paul Abbott

Reputation: 7211

Kinda ugly but

select Names.Name, CustAddress.Address, CustPhone.Phone from
(select Name from CustAddress union select Name from CustPhone) as Names
left join CustAddress on Names.Name = CustAddress.Name
left join CustPhone on Names.Name = CustPhone.Name;

Upvotes: 0

Related Questions