Reputation: 897
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
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
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