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

If a patient have done multiple name changes there will be one row for each change.

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?

Thank you!

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?

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.

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

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.

Thanks!

Erik

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)