Self join sub query help
763735Mar 30 2010 — edited Mar 31 2010This is my table
Doctor Appointment Date Rating
111 23/12/2009 G1
111 23/12/2009 G2
111 23/12/2009 B
111 23/12/2009 D
111 23/12/2008 G1
111 23/12/2008 B
111 23/12/2007 G2
111 23/12/2007 B
111 23/12/2006 D
For Doctor 111, on a given appointment date he has multiple ratings.
I have to fetch the rows (with doctor id, appointment date)
I) which does not have G1 rating but has G2 rating for each appointment date
And vice versa ( does not have G2 rating but has G1 rating for each appointment date)
And
II) if the particular appointment date has both G1 and G2 rating then I can ignore that appointment date – i.e do not fetch any rows if that appointment date has both G1 and G2 ratings.
And
III) if the appointment date has no G1 or G2 rating, then display the appointment date and the doctor.
So, my result set should have the output from all the three conditions for each doctor.
I am not sure how it works. I am trying using correlated subqueries any suggestions???