Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My table looks like this:

ID     APPLICATION    OTHERVALUE
0001   subapp         SubvalueA
0001   mainapp        MainValue1
0002   subapp         SubvalueB
0002   mainapp        MainValue1
0003   subapp         SubvalueA
0003   mainapp        MainValue2

I need all lines of subapp, but then I also need value of OtherValue when the application is mainapp from same table where ID is the same. I'm using Oracle.

How can I build a query for this?

Expected result:

ID     APPLICATION    OTHERVALUE    OTHERVALUE2
0001   subapp         SubvalueA     MainValue1
0002   subapp         SubvalueB     MainValue1
0003   subapp         SubvalueA     MainValue2
share|improve this question
    
You can use the {} button to format your code, tables and the likes. –  dezso Nov 13 '12 at 8:33
add comment

3 Answers

You can use an aggregate function and a CASE statement to get the data into this format:

select id,
  max(case when application ='subapp' then application end) application,
  max(case when application ='subapp' then othervalue end) othervalue,
  max(case when application ='mainapp' then othervalue end) othervalue2
from yourtable
group by id;

See SQL Fiddle with demo

Result:

| ID | APPLICATION | OTHERVALUE | OTHERVALUE2 |
-----------------------------------------------
|  1 |      subapp |  SubvalueA |  MainValue1 |
|  2 |      subapp |  SubvalueB |  MainValue1 |
|  3 |      subapp |  SubvalueA |  MainValue2 |
share|improve this answer
add comment

The following statement should give you the wanted result. I haven't tested it!

select t1.id, t1.application, t1.othervalue, t2.othervalue
from 
    yourtable t1
    JOIN yourtable t2 ON t1.id = t2.id
where 
    t1.application = 'subapp'
    and t2.application = 'mainapp'
order by t1.id;

(Replace yourtable in the FROM clause with your table name.)

share|improve this answer
    
Perfect! Thanks! –  Jukka Nov 13 '12 at 7:51
add comment

Hanspeter Oberlin and blueefeet both have good working solutions either of which may be preferable +1. Here is yet another solution with it's own pros and cons.

SELECT * FROM (
   SELECT ID, Application, OtherValue 
      , MIN(OtherValue) OVER (PARTITION BY ID ORDER BY Application) OtherValue2
   FROM yourtable
)
WHERE Application='subapp';  

drop table yourtable;
create table yourtable (ID Varchar2(4), Application varchar2(7), OtherValue Varchar2(10));
insert into yourtable values ('0001', 'subapp',  'SubvalueA');
insert into yourtable values ('0001', 'mainapp', 'MainValue1');
insert into yourtable values ('0002', 'subapp',  'SubvalueB');
insert into yourtable values ('0002', 'mainapp', 'MainValue1');
insert into yourtable values ('0003', 'subapp',  'SubvalueA');
insert into yourtable values ('0003', 'mainapp', 'MainValue2');
commit;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.