Re: Simple Question for a Oracle/SQL Guru

From: Kbat <kbatsche_at_yahoo.com>
Date: Wed, 8 Dec 1999 22:11:02 -0700
Message-ID: <82ndf4$og0$1@nnrp03.primenet.com>

I haven't been able to test this but it should work...

Select A.Patient_no, A.Appointment.Sv_date, B.Appointment.Sv_date from Appointment A, Appointment B
where A.Patient_no = B.Patient_no and

           A.D_name ='Miss Brown' and B.D_name ='Mr Hussein' and
           A.Sv_date between to_date('01-AUG-99') and to_date('30-SEP-99')
and
           B.Sv_date between to_date('01-AUG-99') and to_date('30-SEP-99');

Better would be

Select C.Patient_Name as Patient,
             A.D_name as Dentist1, A.Appointment.Sv_date as Appointment1,
             B.D_name as Dentist2, B.Appointment.Sv_date as Appointment2,

from Appointment A, Appointment B, Patient C where A.Patient_no = B.Patient_no and

            A.Patient_no = C.Patient_no and
           A.D_name ='Miss Brown' and B.D_name ='Mr Hussein' and
           A.Sv_date between to_date('01-AUG-99') and to_date('30-SEP-99')
and
           B.Sv_date between to_date('01-AUG-99') and to_date('30-SEP-99');

If you don't want to specify the dentists' names you could replace
           A.D_name ='Miss Brown' and B.D_name ='Mr Hussein' and
with
          A.D_name <> B.D_name and

This will give you patients that saw 2 different dentists in the time period specified

for more fun stuff get Celko's book 'SQL for Smarties'

Jaswinder Suri <Jaswinder.Suri_at_msdw.com> wrote in message news:384E244E.C79D9B37_at_msdw.com...

I am a novice at writing SQL commands to manipulate an Oracle database and am having difficulty combining the following Select statements that determine -
Which patients
were seen by two Dentist during the months of August and September. Select 1
Select Appointment.Patient_no, Patient.Patient_no, Appointment.Sv_date from Appointment, Patient
where Appointment.Patient_no = Patient.Patient_no and

          Appointment.D_name ='Miss Brown' and
          Appointment.Sv_date Between to_date('01-AUG-99') and

to_date('30-SEP-99');
Which returned the following result

Patie     Patie     Sv_Date


-----     -----     ---------

f4221 f4221 24-AUG-99
M1321 M1321 30-SEP-99
And also
Select 2
Select Appointment.Patient_no, Patient.Patient_no, Appointment.Sv_date from Appointment, Patient
where Appointment.Patient_no = Patient.Patient_no and

          Appointment.D_name ='Mr Hussein' and
          Appointment.Sv_date Between to_date('01-AUG-99') and

to_date('30-SEP-99');
Which returned the following result

Patie     Patie     Sv_Date


-----     -----     ---------


f4221    f4221   21-AUG-99

f4221 f4221 17-SEP-99
f4221 f4221 18-SEP-99
Problem is If i want to determine from the outputs which Patient saw both Dentist between
the months of August and September i would combine the select statements above, as follows
1 Select Appointment.Patient_no, Patient.Patient_no, Appointment.Sv_date 2 from Appointment, Patient
3 where Appointment.Patient_no = Patient.Patient_no and

4             Appointment.D_name ='Mr Hussein' and
5             Appointment.Sv_date Between to_date('01-AUG-99') and
6
to_date('30-SEP-99')                      IN
7          (Select Appointment.Patient_no, Patient.Patient_no,
Appointment.Sv_date
8          from Appointment, Patient
9          where Appointment.Patient_no = Patient.Patient_no and
10                  Appointment.D_name ='Miss Brown' and
11                  Appointment.Sv_date Between to_date('01-AUG-99') and

to_date('30-SEP-99'));
I keep getting an Error at line 6

           IN
           *

ORA-00933: SQL command not properly ended Can anyone please tell me what i am doing wrong. I just can not see the mistake.
Thank you for helping,
Bye.
Jas Received on Wed Dec 08 1999 - 23:11:02 CST