Complex Sql Query Help. Forming query in SQL

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.

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

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.

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!