Multipart identifier issue

1

Can someone please help. I receive the multipart identifier error message on Language.LanguageID of this query.

 SELECT DISTINCT
 --Appointments.ApptStart
  --DoctorFacility.ListName
  pp.searchname
 , pp.Sex
 , pp.Birthdate
 , PP.Zip
 , DATEDIFF(HOUR,pp.Birthdate,'2014-12-31')/8766 as Age
 , PatientInsurance.InsuredId
 , pp.LanguageId
 , MAX(CASE WHEN race.Description IN ('Unspecified','Black or African American') THEN 
		CASE WHEN subrace.description = 'African American' THEN  subrace.Description 
		WHEN subrace.description IN ('Unknown','Undetermined') THEN 'Unknown'  
		ELSE subrace.description END
        ELSE race.Description END)  AS race /* Only using 1 race for now */

 , eth.Description 
 FROM   CentricityPS.dbo.Appointments Appointments 
 INNER JOIN CentricityPS.dbo.DoctorFacility DoctorFacility 
	ON Appointments.ResourceId=DoctorFacility.DoctorFacilityId
LEFT OUTER JOIN CentricityPS.dbo.PatientProfile pp 
	ON Appointments.OwnerId=pp.PatientProfileId 
LEFT OUTER JOIN CentricityPS.dbo.MedLists ApptStatus 
	ON Appointments.ApptStatusMId=ApptStatus.MedListsId
LEFT OUTER JOIN CentricityPS.dbo.PatientInsurance PatientInsurance 
	ON pp.PatientProfileId=PatientInsurance.PatientProfileId

LEFT OUTER JOIN  PatientProfile PatientProfile 
    ON pp.LanguageId=Language.LanguageId

LEFT JOIN MedLists eth
	ON pp.EthnicityMId = eth.MedListsId
LEFT JOIN PatientRace pr
	ON pp.PId = pr.pid
LEFT JOIN MedLists race
	on pr.PatientRaceMid = race.MedListsId
LEFT OUTER JOIN dbo.medlists subrace 
	ON pr.PatientRaceSubCategoryMid = subrace.medlistsID

 WHERE  
	PatientInsurance.InsuredId LIKE 'NHP%')

Thank you.

2

You don't have a table called LANGUAGE and no table aliased as LANGUAGE. Which table do you want to join on here:

LEFT OUTER JOIN  PatientProfile PatientProfile 
    ON pp.LanguageId=Language.LanguageId
1 Like
3

Thank you for your quick reply

I need to join PatientProfile.LanguageID to Language.LanguageID

Thank you,

4

Do you have a table in your database called Language? If there is, that table needs to be listed in the FROM clause. How that table relates to the other tables that you have now depends on what columns you have in that table, and the logic to be used to relate them to existing tables.

You said want to relate PatientProfile.LanguageID to Language.LanguageID. That connects those two tables. But how does that pair connect to other tables? When you have a join condition, it has to refer to the tables that you have listed previously in the FROM clause.

1 Like
5

I will have to take a look at that. Thank you. If I have more questions will post.

6

So I have changed the code a little. Now I receive an error message : Incorrect syntax near CASE.

SELECT Distinct    PatientProfile.First, 
PatientProfile.Last, 
PatientProfile.Zip, 
PatientProfile.Birthdate, 
PatientInsurance.InsuredId, 
Language.Code 

MAX(CASE WHEN race.Description IN ('Unspecified','Black or African American') THEN 
		CASE WHEN subrace.description = 'African American' THEN  subrace.Description 
		WHEN subrace.description IN ('Unknown','Undetermined') THEN 'Unknown'  
		ELSE subrace.description END
        ELSE race.Description END)  AS race /* Only using 1 race for now */
        , eth.description

FROM         PatientProfile INNER JOIN
                      PatientInsurance ON PatientProfile.PatientProfileId = PatientInsurance.PatientProfileId 
                      INNER JOIN
                      PatientRace ON PatientProfile.PatientProfileId = PatientRace.PatientProfileId AND PatientProfile.PId = PatientRace.PID 
                      INNER JOIN
                      Language ON PatientProfile.LanguageId = Language.LanguageId
                      LEFT JOIN MedLists eth
	ON pp.EthnicityMId = eth.MedListsId
inner JOIN PatientRace pr
	ON pp.PId = pr.pid
inner JOIN MedLists race
	on pr.PatientRaceMid = race.MedListsId
 inner JOIN dbo.medlists subrace 
	ON pr.PatientRaceSubCategoryMid = subrace.medlistsID
                     
                      
                      where PatientInsurance.InsuredId like 'NHP%'
                      group by PatientProfile.Last
7

Need a comma after Language.Code