• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

SQL 2005 Query

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.
0
dmanisit
Asked:
dmanisit
  • 6
  • 5
1 Solution
 
Ephraim WangoyaCommented:
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

0
 
dmanisitAuthor 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  
0
 
Ephraim WangoyaCommented:

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

0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
dmanisitAuthor 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
0
 
Ephraim WangoyaCommented:

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
0
 
dmanisitAuthor Commented:
It is going to be another field to join
0
 
Ephraim WangoyaCommented:

Can you show each table and its fields
0
 
dmanisitAuthor 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
0
 
dmanisitAuthor Commented:
This crap just blows my mind
0
 
Ephraim WangoyaCommented:
so you need to join with Account_ID
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_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

0
 
dmanisitAuthor Commented:
Thank you so much. One day I might be able to do it
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now