Complex Sql Query Help. Forming query in SQL

Question

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.