question

kane avatar image
kane 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'
sqlsql-serversql server
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
David Wimbush avatar image
David Wimbush answered
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).
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

kane avatar image kane commented ·
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
0 Likes 0 ·
Mart avatar image Mart kane commented ·
Hi Kane if David has answered your question would you mark the question as answered so credit is given Cheers!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.