I am NOT a SQL expert (obviously). I am learning things as I go because I have to. I have several different queries in which I am having to do a LOT of joins (which probably aren't done efficiently), and then, within that, I am having to manually create a lot of matches to information because I don't know how to get it into one query. It may not be possible, but is there a way to add into the query the ability to perform these additional links?
I am having to constantly update the list of providers' first names, last names, etc. in all of these scripts as people come and go, and I cannot even find a way to pull all of a patient's phone numbers on one single line. So these are two similar but not identical situations.
I tried this (below) for one of the queries, but it gives me multiple lines, so not what I need because the table that has the phone numbers has as many entries for the patient entity_id as the patient has phone numbers in the system.
,CASE PHON.[PhoneType_Description] WHEN 'Home' THEN PHON.[Phone_Number] + ' ' + PHON.[PhoneType_Description] WHEN 'Cell' THEN PHON.[Phone_Number] + ' ' + PHON.[PhoneType_Description] END AS 'Phone'
So I get:
Facility ID Resource Appointment start time Appointment Description Patient First Name Patient Last Name Phone Clinic 1 Jones 3/1/2022 9:00 3 month follow up Jenny Smith (123)456-7890 Cell Clinic 1 Jones 3/1/2022 9:00 3 month follow up Jenny Smith (123)867-5309 Home
Which has all the info I need, but I need the 2 phone numbers on one line... In this example, there is a table that gets me the patient entity ID, but that Entity ID has numerous entries in the "ENTY_phoneDetail" table (one for each phone number). This table has a Phone-ID and a PhoneType_ID, but not the phone numbers. They are in the ENTY_Phone table with the Phone_ID.
SO, I am pulling an entity-Id through several joins to get that far down. I can pull the phone used for scheduling the appointment because that phone ID is listed in an appointment table. However, pulling the patient with PhoneType_ID "1" which equals home, and PhoneType_ID "2" which equals Cell, etc. in separate columns on the same line has proven impossible for me.
Probably something simple for people who know what they are doing, right? And maybe it is just MORE joins???? It is also difficult to clearly demonstrate what I am dealing with because I am in a production healthcare database and cannot post any information I am actually pulling from this query.