title: SQL Query help (possibly use JOIN?)
body: <p>Table A [PATIENT] has columns [PATID], [FIRSTVISITDATE]</p>
  
  <p>Table B [APPT] has columns [APPTID], [PATID], [CREATEDATE]</p>
  
  <p>Table C [NOTE] has columns [NOTEID], [NOTETEXT]</p>
  
  <p>Table D [PROCS] has column [PROCID], [PATID]</p>
  
  <p>Table E [CHARGE] has columns [CHARGEID], [AMOUNT]</p>
  
  <p>I need to sum CHARGE(AMOUNT) by PATID for all PATIENTS where the NOTE.NOTETEXT contains 'text' and one of the APPT  for a PATIENT containing the 'text' has an APPT.CREATEDATE = to the PATIENT.FIRSTVISITDATE</p>
  
  <p>Simply put I need to SUM the charges for PATIENTS if they have an appointment with 'text' in their notes and the appointment with that 'text' was their first visit to the office</p>
  
  <p>Other key points:</p>
  
  <p>CHARGE.CHARGEID = PROC.PROCID</p>
  
  <p>NOTE.NOTEID = APPT.APPTID</p>
  
  <p>With my limited knowledge of SQL I was able to sum for all patients regardless if the 'text' was included in their first appointments notes and for that I used:</p>
  
  <pre><code>select (SUM(AMOUNT)) as 'Cash Payments' from CHARGE where CHARGEID in
    (select PROCID from PROC where PATID in
       (select PATID from APPT where APPTID in
          (select NOTEID from NOTE where NOTETEXT like '%text%')))
  </code></pre>