Reputation: 33
Self Join Oracle SQL
i have this table :
CREATE TABLE ASSETS
(
AID INTEGER DEFAULT ON NULL assets_id_seq.nextval
, ANAME VARCHAR2(30)
, ATYPE VARCHAR2(20)
, SUB_ID NUMBER
, CONSTRAINT ASSETS_PK PRIMARY KEY
(
AID
)
ENABLE
);
I inserted couple of values, as shown here:
aid aname atype subscriber_phone
1 superfast internet 8 adsl 42504556
4 line fixed line 42504556
5 superfast internet 32 adsl 42551344
6 line fixed line 42551344
7 superfast internet 50 adsl 49111222
8 line fixed line 49111222
9 line fixed line 49000000
10 line fixed line 49555333
11 line fixed line 49000323
12 line fixed line 49000131
i want to get each subscriber_phone and their aname in one recored for all, like this:
subscriber_phone aname_adsl aname_fixed_line
42504556 superfast internet 8 line
42551344 superfast internet 32 line
49111222 superfast internet 50 line
49000000 null line
49555333 null line
49000323 null line
49000131 null line
what i did is a self join:
select a.sub_id,a.aname,b.aname
from
assets a, assets b
where
a.sub_id = b.sub_id and a.atype <> b.atype
and this what i get :
subscriber_phone aname aname1
42504556 line superfast internet 8
42504556 superfast internet 8 line
42551344 line superfast internet 32
42551344 superfast internet 32 line
49111222 line superfast internet 50
49111222 superfast internet 50 line
any help ?
Upvotes: 0
Views: 82
Answers (3)
Reputation: 191315
You could use a pivot instead of a self-join:
select sub_id, adsl, fixed_line
from (
select aname, atype, sub_id
from assets
where atype in ('adsl', 'fixed line')
)
pivot (
max(aname) for (atype) in ('adsl' as adsl, 'fixed line' as fixed_line)
) p
order by sub_id;
SUB_ID ADSL FIXED_LINE
---------- ------------------------------ ------------------------------
42504556 superfast internet 8 line
42551344 superfast internet 32 line
49000000 line
49000131 line
49000323 line
49111222 superfast internet 50 line
49555333 line
7 rows selected.
If you really wanted to self-join then you could use subqueries to get the different types, then do a full outer join:
select coalesce(a.sub_id, b.sub_id) as sub_id, a.aname, b.aname
from (
select sub_id, aname
from assets
where atype = 'adsl'
) a
full outer join (
select sub_id, aname
from assets
where atype = 'fixed line'
) b
on a.sub_id = b.sub_id
order by sub_id;
SUB_ID ANAME ANAME
---------- ------------------------------ ------------------------------
42504556 superfast internet 8 line
42551344 superfast internet 32 line
49000000 line
49000131 line
49000323 line
49111222 superfast internet 50 line
49555333 line
7 rows selected.
... but the pivot is cleaner, and easier to extend to more types/column later if needed.
If you can't have ADSL without a fixed line then it's a bit simpler - you don't need a full outer join, or the subqueries:
select b.sub_id, a.aname, b.aname
from assets b
left join assets a
on a.sub_id = b.sub_id
and a.atype = 'adsl'
where b.atype = 'fixed line'
order by sub_id;
SUB_ID ANAME ANAME
---------- ------------------------------ ------------------------------
42504556 superfast internet 8 line
42551344 superfast internet 32 line
49000000 line
49000131 line
49000323 line
49111222 superfast internet 50 line
49555333 line
7 rows selected.
Upvotes: 1
Reputation: 1270081
You can do what you want using a self-join. It looks like:
select coalesce(a_adsl.sub_id, a_fl.sub_id) as sub_id,
a.aname as dsl, b.aname as fixed_line
from (select a_adsl.*
from assets a_adsl
where type = 'adsl'
) a_adsl full join
(select a_fl.*
from assets a_fl
where type = 'fixed line'
) a_fl
on a_adsl.sub_id = a_fl.sub_id ;
Personally, I prefer the aggregation approach. But this appears to the what you are trying to implement.
Actually, you could also do this with a left join and fewer subqueries:
select a1.sub_id as sub_id,
(case when a1.type = 'adsl' then a1.name
when a2.type = 'adsl' then a2.name
end) as adsl,
(case when a1.type = 'fixed line' then a1.name
when a2.type = 'fixed Line' then a2.name
end) as fixed_line
from assets a1 left join
assets a1
on a1.sub_id = a2.sub_id and a1.name < a2.name;
Upvotes: 0
Reputation: 142798
Alternatively:
SQL> select a.subscriber_phone,
2 max(case when a.atype = 'adsl' then a.aname end) aname_adsl,
3 max(case when a.atype = 'fixed line' then a.aname end) aname_fixed_line
4 From test a
5 group by a.subscriber_phone
6 order by subscriber_phone;
SUBSCRIB ANAME_ADSL ANAME_FIXED_LINE
-------- --------------------- ---------------------
42504556 superfast internet 8 line
42551344 superfast internet 32 line
49000000 line
49000131 line
49000323 line
49111222 superfast internet 50 line
49555333 line
7 rows selected.
SQL>
Upvotes: 1