asked on

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.

Ephraim Wangoya

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

dmanisit

ASKER

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 Wangoya

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

dmanisit

ASKER

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 Wangoya

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

dmanisit

ASKER

It is going to be another field to join

Ephraim Wangoya

Can you show each table and its fields

dmanisit

ASKER

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

dmanisit

ASKER

This crap just blows my mind

ASKER CERTIFIED SOLUTION

Ephraim Wangoya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dmanisit

ASKER

Thank you so much. One day I might be able to do it