Combining different tables and extracting data on conditions
I am going through some kind of problem. Here is the table schema. I have
two tables job,application.
Application: aid,aname,stime,jname
Job:jid,jname,aid,start,end
Application table:
aid aname stime
A ABC 23-SEP-13
B DEF 24-SEP-13
Job table:
jid jname aid start end
1 job1 A 10-OCT-13 13:06:20 11-OCT-13 13:06:45
2 job2 A 10-OCT-13 14:06:20 11-OCT-13 14:09:55
3 job1 B 10-OCT-13 15:16:20 11-OCT-13 15:06:45
4 job2 B 10-OCT-13 15:26:20 11-OCT-13 15:46:45
I need the output as follows. I need to generate the differences between
the start and end times of all the jobs in every application.
aname stime jname (end-start)Days Hours Minutes Seconds
ABC 23-SEP-13 job1 1 0 0 25
ABC 23-SEP-13 job2 1 0 3 35
DEF 24-SEP-13 job1 1 0 10 25
DEF 24-SEP-13 job2 1 0 20 25
I tried using in clause to extract but here the problem is I am unable to
retrieve multiple columns from the second table. Thank you.
