Re: Simple Question for a Oracle/SQL Guru

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 8 Dec 1999 09:36:25 -0000
Message-ID: <944645979.6748.1.nnrp-09.9e984b29@news.demon.co.uk>

One option, if you do not need to see the dates is simply to intersect the two results:

select ........
INTERSECT
select .......

This produces a unique list of
exactly those items that appear in
both select statements.

Another alternative is

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

    Jaswinder Suri wrote in message <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 - 03:36:25 CST