Problem with add extra AND to query

[sup919]

hi there i ve got a problem that shouldnt be difficult to solve but since i m quite new to this i got it totally wrong...
Basicaly i manage to use the except with date condition but when i added another AND condition it give me a whole list of answer which it shouldnt
This is my sql structure

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

Note: without having AND Patient.Gender =1 the query work fine

please help
much appreciate
sun

 

[bborissov]

What you are trying to do with that query?
Can you post some data and desired result?
With your query you ask for DISTICT records of:

SELECT Patient.HNID,
       Patient.HN,
       Patient.Names,
       Patient.Surname
FROM   Patient

which are not included in:

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

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLBill]

Sometimes parenthesis can be important. Try this:

WHERE  (Appointment.AppointmentDate Between '20060704' AND '20061004') AND Patient.Gender = 1

-SQLBill

Posting advice: FAQ481-4875

 

[sup919]

I ve try to put the bracket between and it does't work
well in my data there is 2 person who doesnt have appointment between 04-07-2006 and 04-10-2006 which is Bill and Sarah but when I added gender condition to it it give me Bill, Sarah , Jack, Shephanie ... so i dont know what s wrong with my query. In my Patient table it contain information about patient including gender and in appointment it include appointment date that patient have visit ....

what i want to do is find the patient who has not been visit for the last 3 months ..

i dont know where i did go wrong with sql query .. please enlighten me

thanks u
sun