#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    6
    Rep Power
    0

    Complex SQL Query on Multiple Tables


    I have three tables.

    Patients
    PatientNotes
    Invoices

    Zero or One patient notes record can exist for any patient. Related column is Id (Patient Id)

    Zero, One or Many invoices can exist for a single patient. Related column is Id (Patient Id)

    The patient's current name (last,first) is stored in the patient record and the name at the time of service is stored in each invoice record. Therefore the name on the last invoice is stored in the patient record.

    I am able to do a left join on the notes table with the patients to list patients with or without notes. If there are notes for the patient the notes detail is returned, else the query returns null for the notes data and this is perfect.

    Here's my requirement: We are looking for AKA's (Also Known As). With each patient record, I need to list the first (or ANY) invoice where the patient's name may be different from the current name i.e from marriage, name change, error etc... If the names on all of the invoices match the patient names exactly then just return null.

    The query results should look something like this with one row for each patient (Additional columns ignored for simplicity here):

    PtLast,PtFirst,PtId,PtNotesYN,InvDate,InvLast,InvFirst,InvNumber

    Thanks is advance,
    Erik
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,377
    Rep Power
    391
    Code:
    select Patients.PtLast,
           Patients.PtFirst,
           Patients.PtId,
           PatientNotes.PtNotesYN,
           Invoices.InvDate,
           Invoices.InvLast,
           Invoices.InvFirst,
           Invoices.InvNumber
      from patients
      left
      join PatientNotes 
        on Patients.PtId = PatientNotes.PtId
      left
      join invoices
        on Patients.PtId = Invoices.PtId
       and (Patients.PtLast <> Invoices.InvLast
        or Patients.PtFirst <> Invoices.InvFirst)
    If a patient have done multiple name changes there will be one row for each change.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    6
    Rep Power
    0
    Thanks for the quick reply and I will give this a try. I can filter out the duplicate results for multiple name changes when displaying to the UI so that will not be an issue. However, is there a way to do that in the query?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,377
    Rep Power
    391
    Code:
    select Patients.PtLast,
           Patients.PtFirst,
           Patients.PtId,
           PatientNotes.PtNotesYN,
           Invoices.InvDate,
           Invoices.InvLast,
           Invoices.InvFirst,
           Invoices.InvNumber
      from patients
      left
      join PatientNotes 
        on Patients.PtId = PatientNotes.PtId
      left
      join (select min(Invoices.InvDate) as InvDate,
                   Invoices.InvLast,
                   Invoices.InvFirst,
                   Invoices.PtId,
                   min(Invoices.InvNumber) as InvNumber
              from invoices
             group
                by Invoices.InvLast,
                   Invoices.InvFirst,
                   Invoices.PtId) as invoices 
        on Patients.PtId = Invoices.PtId
       and (Patients.PtLast <> Invoices.InvLast
        or Patients.PtFirst <> Invoices.InvFirst)
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    6
    Rep Power
    0
    Thank you!
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    6
    Rep Power
    0
    One more thought here...

    When use the query that yields one row for each invoice on a name change I get what I expect. However, when I use the second query, I run into an error "msg 207, level 16, line # Invalid column name" when I include any column after the fifth column in the invoices table. Do you know what might cause this?
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,377
    Rep Power
    391
    It is due to the fact that only those columns present in the derived table can be selected in the outmost select list. If you want to select further columns from the invoice table these must be included in the select list of the derived table.

    The select list of the derived table will depend on what data you have when there are multiple invoices with the same name change. For instance, would you like to retrive all data from the invoice with the lowest date within that group? You need to describe how this should be assorted.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    6
    Rep Power
    0
    Thanks again. I hate to keep asking questions and taking up your time. Can you recommend any comprehensive resources on the web (or in print) for learning SQL in depth. I've been through some basic tutorials etc... but I think that I am going to need more than is available on the basic self help websites that are out there.

    Erik

    Originally Posted by swampBoogie
    It is due to the fact that only those columns present in the derived table can be selected in the outmost select list. If you want to select further columns from the invoice table these must be included in the select list of the derived table.

    The select list of the derived table will depend on what data you have when there are multiple invoices with the same name change. For instance, would you like to retrive all data from the invoice with the lowest date within that group? You need to describe how this should be assorted.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,377
    Rep Power
    391
    There is books on line for SQL server

    http://msdn.microsoft.com/en-us/library/bb510741.aspx

    For advanced SQL, look for books by Joe Celko.

    For books on less than advanced I don't have any recommendations.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    6
    Rep Power
    0
    Thanks!

    Erik
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,146
    Rep Power
    4274
    Originally Posted by swampBoogie
    For books on less than advanced I don't have any recommendations.
    i can recommend my book, it's aimed at beginners and will move them to intermediate, and it's vendor-agnostic (but does mention mysql more than others in the sidebars)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo