From the following tables, write a query to get the histogram of specialties of the unique physicians who have done the procedures

Question

From the following tables, write a query to get the histogram of specialties of the unique physicians who have done the procedures but never did prescribe anything.

Patient treatment table

Patient Id | Event Name | Physician ID
1 Radiation 1000
2 Chemotherapy 2000
1 Biopsy 1000
3 Immunosuppressants 2000
4 BTKI 3000
5 Radiation 4000
4 Chemotherapy 2000
1 Biopsy 5000
6 Chemotherapy 6000

Event category table

Event Name | Category
Chemotherapy Procedure
Radiation Procedure
Immunosuppressants Prescription
BTKI Prescription
Biopsy Test

Physician Speciality Table

Physician Id | specialty
1000 Radiologist
2000 Oncologist
3000 Hematologist
4000 Oncologist
5000 Pathologist
6000 Oncologist

Sample Output:

specialty speciality_count
Oncologist 2
Radiologist 1

the database i am using is Mysql

Answer 1

I threw this Fiddle together: http://sqlfiddle.com/#!9/2481c3/4/0

Does this query turn up what you're looking for?

select specialty, count(*) specialty_count from pst
where physician_id not in 
 (select physician_id from ptt where event_name in 
  (select event_name from ect where category='prescription'))
and physician_id in
 (select physician_id from ptt where event_name in 
  (select event_name from ect where category='procedure'))
group by specialty

It is using sub queries to filter the physician ids and counting the remaining specialties.