Clever Geek Handbook

Complicated SQL reference. Forming a query in SQL

I am new to SQL and I need to write a complex query. Could you help me?

I have two tables. One is called PATIENTS and the other is called CASES. Patients have a "patient number" and an entered date. CASES has "patient number", "case number". and "change date". Two tables are associated with a "patient number". There are several “no cases”. associated with one "patient number", since one patient can have multiple cases.

I need to get the following entries. All patients (from PATIENTS) who have all "sick date" are older than a certain date. So if the date is June 20, 1999. Then I need all patients who have not had cases changed after 06-20-1999

Any help would be appreciated. Thank.

+2


source to share


3 answers 


SELECT
    *
FROM
    Patients
WHERE
    PatientId NOT IN(
        SELECT
            PatientId
        FROM
            Cases
        WHERE
            DateModified >= '06-20-1999'
    )


+11


source 


SELECT patient_no
FROM patients
WHERE patient_no NOT IN (
  SELECT patient_no
  FROM cases
  WHERE date_modified >= '1999-06-20'
)


Not sure about this date format.

+1


source


If you only want patients and ALL cases need to be changed by date, then

Select * From Patients p
Where Exists      -- eliminates those with no cases/or no cases before date
    (Select * From cases 
     Where PatientNo = p.PatientNo
        And ModifiedDate < [DateValue])
  And Not Exists --  to eliminate patients with cases modified after date.. 
    (Select * From cases 
     Where PatientNo = p.PatientNo
        And ModifiedDate >= [DateValue])

If you need case data, use the connection:

Select * From Patients p 
   Join Cases c on c.PatietNo = p.PatientNo 
Where c.Modifed < DateValue

EDIT: change the value still to @Larry's comment below, thanks!

0


source






More articles:


All Articles