SQL Server Performance

problem with using AND query

Discussion in 'SQL Server 2005 General Developer Questions' started by sun919, Oct 4, 2006.

  1. sun919 New Member

    Hi there,
    I ve got a quick question concerning using AND in a query. Basically i want to make a query that patient have miss appoinment during the last 3 months who is male ..
    Well with my query it work fine with date condition but when i added gender condition it give me more answers than previous query which should be opposite :
    i.e. with current query it give john and mary but adding condition gender to this gave me john mary susan alex
    this is my query that i ve written

    SELECT Patient.HNID, Patient.HN, Patient.Names, Patient.Surname
    FROM Patient
    EXCEPT Select Patient.HNID,Patient.HN,Patient.Names, Patient.Surname
    FROM INNER Patient JOIN Appointment ON Patient.HNID = Appointment.HNID
    WHERE (Appointment.AppointmentDate Between '20060704' AND '20061004') AND Patient.Gender = 1

    please helps
    many thanks
    sun
  2. Roji. P. Thomas New Member

  3. Twan New Member

    Hi ya,

    anything to do with the EXCEPT? the clause you are adding reduces the result set of the EXCEPT and therefore increases the select from Patient

    Cheers
    Twan
  4. Madhivanan Moderator

    by EXCEPT did you mean not exists?
    Post some sample data and the result you want

    Madhivanan

    Failing to plan is Planning to fail
  5. sun919 New Member

    sample -------- Patient(HNID) .... Name ....... gender....... Another table Appointment ....... Patient HNID ........ Appointment Date
    John............ 1(male)......................................................................(same as John) .......... 15/8/2006
    Alex............ 1....................................................................................................................15/4/2006
    Mary..........2.....................................................................................................................15/9/2006
    Susan............2..................................................................................................................18/11/2006
    This r example of my information for the table
    thanks
    sun
  6. sun919 New Member

    what i want in the first round with out gender is John and Mary and with gender = 1 then i want just mary
  7. Adriaan New Member

    I'm not (yet) familiar with the EXCEPT syntax (SQL 2005?) but I would guess that you are in fact finding patients with gender <> 1.
  8. Twan New Member


    SELECTp.HNID, p.HN, p.Names, p.Surname
    FROMPatient p
    WHEREp.Gender = 1
    EXCEPT
    Select p.HNID,p.HN,p.Names, p.Surname
    FROM Patient INNER JOIN Appointment
    ON p.HNID = a.HNID
    WHERE (a.AppointmentDate Between '20060704' AND '20061004')


    ought to work

    alertnatively


    SELECTp.HNID, p.HN, p.Names, p.Surname
    FROMPatient p
    WHEREp.Gender = 1
    ANDp.HNID not in (selectHNID
    fromAppointment
    whereappointmentdate between '20060704' and '20061004' )


    Cheers
    Twan
  9. Roji. P. Thomas New Member

  10. sun919 New Member

    Thanks u very one, Once i did what Twan suggested, it works

Share This Page