Complex Sql Query Help. Forming query in SQL

Question

Hello,

I am new to SQL and need to write a complex query. Can you please help?

I have two tables. One is called PATIENTS and the other one is called CASES. PATIENTS has a "patient number" and a date entered. CASES has "patient number," "case no." and "date modified." The two tables are connected with the "patient number." There are multiple "case no." associated with one "patient number" since one patient can have multiple cases.

I need to get the following records. All the patients (from PATIENTS) that have all the "cases modified date" older than a certain date. So if the date is June 20th 1999. Then I need all the patients, who have had no cases modified after 06-20-1999

I will appreciate any help. Thank you.

Answer 1

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

Answer 2

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

Not sure about that date format though.

Answer 3

If all you need is the patients, and ALL the cases have to be modified before the 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 as well, use a join:

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

EDIT: to change the after to before from @Larry's comment below, thx!