asked
Ambiguous column name
I need to join two tables that have the same column name. I have been trying to do alias and work through this and I am just stuck. I am a novice to SQL so if someone could please help. The common column is ResidentKey. with PhoneCTE as ( select ResidentContactKey , rt_Telephone , row_number() over (partition by ResidentContactKey order by rt_Telephone) as RowNum from dbo.ResidentTelephone ) Select ResidentKey ,rc_created , (rc_LastName) as 'Sort Name' , (rc_FirstName + ' ' + rc_LastName) as Name , rc_Address ,rc_Address2 ,rc_City ,rc_State ,rc_Zipcode ,isnull(PhoneCTE1.rt_Telephone, '') as 'Home Phone' ,isnull(PhoneCTE2.rt_Telephone, '') as 'Addl Phone' ,rc_email as 'PersemailAddr' From dbo.ResidentContact JOIN dbo.Resident ON (ResidentContact.ResidentKey = Resident.ResidentKey) left join PhoneCTE PhoneCTE1 ON PhoneCTE1.ResidentContactKey = ResidentContact.ResidentContactKey and PhoneCTE1.RowNum = 1 left join PhoneCTE PhoneCTE2 ON PhoneCTE2.ResidentContactKey = ResidentContact.ResidentContactKey and PhoneCTE2.RowNum = 2 Where ResidentKey='13317'
I assume you mean ResidentKey? Just prefix it with the table name (eg ResidentContact.ResidentKey) or the alias name if you used one (eg where I said PhoneCTE1.ResidentContactKey).
That was stupid I was reading the error wrong and all I needed to do is add ResidentContact.ResidentKey and that worked. Sometimes the you stare at things so long I needed someone to tell me the obvious. Thanks again for the help. Kane
18 People are following this question.
