CODEWITHSUNDEEP
oracle-database
Deepan
Deepan

Reputation: 127

Oracle SQL -Retrieving Master and Child's data

I have the below mentioned table structure.

Master-Child Table:

Master_Job_Id    Child_Job_Id
M1                  C1
M1                  C2

Amount Table(This table contains the details of Child and Master Job Ids):

Job_Id      Rate_Id         Amount
M1              R1            50
C1              R1            10
C2              R2            25

Rate Table

Rate_Id     Lang_Id
R1              L1
R2              L2

Here M1 Job Id has two Childs C1 and C2. I would like to frame a query which would provide me the below data:

Job_Id      Lang_Id         Amount
M1              L1              60(Since M1 has two L1 values - that of M1 and C1, the amounts are added so 60 should come)
M1              L2              25(Here M1 has only one L2 value so the total amount value is 25)

Any help on this is much appreciated.

Regards,

Upvotes: 1

Views: 33

Answers (1)

sagi
sagi

Reputation: 40481

This should work:

SELECT t.master_job_id,r.Lang_Id,sum(a.amount)
FROM Master_Child t
INNER JOIN amount a
 ON(a.job_id in(t.master_job_id,t.child_job_id))
INNER JOIN Rate r
 ON(a.rate_id = r.rate_id)
GROUP BY t.master_job_id,r.lang_id

Upvotes: 1

Related Questions