We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL 2005 Query

Medium Priority
290 Views
Last Modified: 2012-05-11
Ok, I am very new to SQL, I have a query but need to add a field to the results I already have, I am having trouble with this. Here is my current Query:

 SELECT * FROM (
SELECT  
                      PM.Patients.Patient_Number, PM.Contacts.Last_Name, PM.Contacts.Middle_Initial, PM.Contacts.First_Name, PM.Contacts.SSN, PM.Contacts.Date_Of_Birth,
                      PM.Appointments.Appointment_DateTime, PM.Appointments.Scheduling_Location_ID, PM.Appointments.Status,
                      ROW_NUMBER() OVER (PARTITION BY PM.Patients.Patient_Number ORDER BY PM.Appointments.Appointment_DateTime) rn
FROM         PM.Appointments INNER JOIN
                      PM.Patients ON PM.Appointments.Patient_ID = PM.Patients.Patient_ID INNER JOIN
                      PM.Contacts ON PM.Patients.Contact_ID = PM.Contacts.Contact_ID
WHERE     (PM.Appointments.Scheduling_Location_ID = 13) AND (PM.Appointments.Status = 'S')) t1 WHERE rn = 1
ORDER BY Last_Name

So I need to add a table called PM.Accounts and the field I need is Account_Type_ID, NOW this is just a numeric value and it is mapped back to the patient through the PM.Patients table, I need to keep the current query I have but add this criteria. One more snag, the actual table that I want to display after matching the Account_Type_ID to the Patient_ID is the table called PM.Account_Types, this is where the Account_Type_ID actually tells me what it is.
Comment
Watch Question

Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:
try
SELECT * 
FROM (SELECT PM.Patients.Patient_Number, PM.Contacts.Last_Name, 
             PM.Contacts.Middle_Initial, PM.Contacts.First_Name, 
             PM.Contacts.SSN, PM.Contacts.Date_Of_Birth, 
             PM.Appointments.Appointment_DateTime, PM.Appointments.Scheduling_Location_ID, 
             PM.Appointments.Status,
             PM.Account_Types.AccountType, --the field with the description
             ROW_NUMBER() OVER (PARTITION BY PM.Patients.Patient_Number ORDER BY PM.Appointments.Appointment_DateTime) rn 
      FROM PM.Appointments 
      INNER JOIN PM.Patients ON PM.Appointments.Patient_ID = PM.Patients.Patient_ID 
      LEFT JOIN PM.Accounts ON PM.Accounts.Patient_ID = PM.Patients.Patient_ID 
      LEFT JOIN  PM.Account_Types ON PM.Account_Types.Account_Type_ID = PM.Accounts.Account_Type_ID
      INNER JOIN PM.Contacts ON PM.Patients.Contact_ID = PM.Contacts.Contact_ID
      WHERE (PM.Appointments.Scheduling_Location_ID = 13) 
      AND (PM.Appointments.Status = 'S')) t1 
WHERE rn = 1
ORDER BY Last_Name

Open in new window

Author

Commented:
Ok so here it is modified, and the error:

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '('.


USE Ntier_TRAINING

SELECT *
FROM (SELECT PM.Patients.Patient_Number, PM.Contacts.Last_Name,
             PM.Contacts.Middle_Initial, PM.Contacts.First_Name,
             PM.Contacts.SSN, PM.Contacts.Date_Of_Birth,
             PM.Appointments.Appointment_DateTime, PM.Appointments.Scheduling_Location_ID,
             PM.Appointments.Status,
             PM.Account_Types.AccountType,Description
             ROW_NUMBER() OVER (PARTITION BY PM.Patients.Patient_Number ORDER BY PM.Appointments.Appointment_DateTime) rn
      FROM PM.Appointments
      INNER JOIN PM.Patients ON PM.Appointments.Patient_ID = PM.Patients.Patient_ID
      LEFT JOIN PM.Accounts ON PM.Accounts.Patient_ID = PM.Patients.Patient_ID
      LEFT JOIN  PM.Account_Types ON PM.Account_Types.Account_Type_ID = PM.Accounts.Account_Type_ID
      INNER JOIN PM.Contacts ON PM.Patients.Contact_ID = PM.Contacts.Contact_ID
      WHERE (PM.Appointments.Scheduling_Location_ID = 13)
      AND (PM.Appointments.Status = 'S')) t1
WHERE rn = 1
ORDER BY Last_Name  
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

You are missing a comma after description
SELECT * 
FROM (SELECT PM.Patients.Patient_Number, PM.Contacts.Last_Name, 
             PM.Contacts.Middle_Initial, PM.Contacts.First_Name, 
             PM.Contacts.SSN, PM.Contacts.Date_Of_Birth, 
             PM.Appointments.Appointment_DateTime, PM.Appointments.Scheduling_Location_ID, 
             PM.Appointments.Status,
             PM.Account_Types.AccountType,Description,
             ROW_NUMBER() OVER (PARTITION BY PM.Patients.Patient_Number ORDER BY PM.Appointments.Appointment_DateTime) rn 
      FROM PM.Appointments 
      INNER JOIN PM.Patients ON PM.Appointments.Patient_ID = PM.Patients.Patient_ID 
      LEFT JOIN PM.Accounts ON PM.Accounts.Patient_ID = PM.Patients.Patient_ID 
      LEFT JOIN  PM.Account_Types ON PM.Account_Types.Account_Type_ID = PM.Accounts.Account_Type_ID
      INNER JOIN PM.Contacts ON PM.Patients.Contact_ID = PM.Contacts.Contact_ID
      WHERE (PM.Appointments.Scheduling_Location_ID = 13) 
      AND (PM.Appointments.Status = 'S')) t1 
WHERE rn = 1
ORDER BY Last_Name

Open in new window

Author

Commented:
Ok so both columns are returning null, I think I see the problem just dont know how to fix it.

here is the whole query

SELECT *
FROM (SELECT PM.Patients.Patient_Number, PM.Contacts.Last_Name,
             PM.Contacts.Middle_Initial, PM.Contacts.First_Name,
             PM.Contacts.SSN, PM.Contacts.Date_Of_Birth,
             PM.Appointments.Appointment_DateTime, PM.Appointments.Scheduling_Location_ID,
             PM.Appointments.Status,
             PM.Account_Types.Account_Type_ID,Description,
             ROW_NUMBER() OVER (PARTITION BY PM.Patients.Patient_Number ORDER BY PM.Appointments.Appointment_DateTime) rn
      FROM PM.Appointments
      INNER JOIN PM.Patients ON PM.Appointments.Patient_ID = PM.Patients.Patient_ID
      LEFT JOIN PM.Accounts ON PM.Accounts.Account_type_ID = PM.Patients.Patient_ID
      LEFT JOIN  PM.Account_Types ON PM.Account_Types.Account_Type_ID = PM.Accounts.Account_Type_ID
      INNER JOIN PM.Contacts ON PM.Patients.Contact_ID = PM.Contacts.Contact_ID
      WHERE (PM.Appointments.Scheduling_Location_ID = 13)
      AND (PM.Appointments.Status = 'S')) t1
WHERE rn = 1
ORDER BY Last_Name

Here is where I think problem is:

 LEFT JOIN  PM.Account_Types ON PM.Account_Types.Account_Type_ID = PM.Accounts.Account_Type_ID
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

I think its here

 LEFT JOIN PM.Accounts ON PM.Accounts.Account_type_ID = PM.Patients.Patient_ID

Is the Accout_type_ID a patient id field or is there another field to join it to patients

Author

Commented:
It is going to be another field to join
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

Can you show each table and its fields

Author

Commented:
So this is what I need: This Query works up till what I need to add:

USE Ntier_JSA


 SELECT * FROM (
SELECT  
                      PM.Patients.Patient_Number, PM.Contacts.Last_Name, PM.Contacts.Middle_Initial, PM.Contacts.First_Name, PM.Contacts.SSN, PM.Contacts.Date_Of_Birth,
                      PM.Appointments.Appointment_DateTime, PM.Appointments.Scheduling_Location_ID, PM.Appointments.Status,
                      ROW_NUMBER() OVER (PARTITION BY PM.Patients.Patient_Number ORDER BY PM.Appointments.Appointment_DateTime) rn
FROM         PM.Appointments INNER JOIN
                      PM.Patients ON PM.Appointments.Patient_ID = PM.Patients.Patient_ID INNER JOIN
                      PM.Contacts ON PM.Patients.Contact_ID = PM.Contacts.Contact_ID
WHERE     (PM.Appointments.Scheduling_Location_ID = 13) AND (PM.Appointments.Status = 'S')) t1 WHERE rn = 1
ORDER BY Last_Name

Now: I need to add just 1 field to the output but it must be matched to that patient. So in the table PM.Accounts is the Account_Type_ID, also in this table there is Account_ID that is relative to the patient we are tryring to match too. So with that said the Account_ID is actually the SAME value that is in another table called PM.Patients and the field is Patient_ID, this is where we will have to match up the correct Account_ID to the Correct Patient_ID. Once thats matched we then need (for the output) to match the Account_Type_ID for that correct patient to output the field called Description

Author

Commented:
This crap just blows my mind
Software Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Thank you so much. One day I might be able to do it
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.