problem with using AND query

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

Can you post a repro?

Roji. P. Thomas
http://toponewithties.blogspot.com

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

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

what i want in the first round with out gender is John and Mary and with gender = 1 then i want just mary

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.

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

EXEPT is not excactly equivalent to NOT EXISTS OR NOT IN When NULLs are involved.

See
http://toponewithties.blogspot.com/2005/07/except-and-intersect.html

Roji. P. Thomas
http://toponewithties.blogspot.com

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