Newbie SQL question

I am doing an accounting analysis of a medical database. I have three tables

GP_doctors, from which I use only 1 column: dr_id_number

Specialist_doctors, from which I use only 1 column: dr_id_number

and

Payments, from which I use 3 columns: amount_paid, patient_id_number, dr_id_number.
Note that any patient_id_number may appear many times, with different dr_id_numbers.
Also, the dr_id_number can be the number of either a GP doctor or a specialist doctor.

I need to construct a view which contains two columns: dr_id_number, sum_of_specialist_payments.

dr_id_number is the number of a GP doctor

sum_of_specialist_payments is defined as follows:

for each GP doctor, find all patient_id_numbers for that GP_doctor. For those same patient_id_numbers, find Payment records which contain dr_id_numbers of Specialist doctors. Compute the sum of of amount_paid for those records as sum_of_specialist payments.

The resulting view thus contains a column of GP doctors and the total amount paid to specialists who have seen the patients of the GP doctors. Received on Wed Nov 20 1996 - 00:00:00 CET