How to perform an INNER JOIN on columns with the same name

Question

I am essentially swapping out the contents of column "LocationId" in the User table with the contents of column "LocationId" in table ChaplainLocation.

I tried a few INNER JOIN statements but have run into issues when it comes to differentiating between the two columns. I also tried setting aliases to no avail.

SELECT a.LocationID FROM [User] AS table1a,
          a.ChaplainId,
          a.FullName,
          b.LocationId FROM [ChaplainLocation] AS table2b,
          b.ChaplainId
   FROM   table1 a
          INNER JOIN table2 b
             ON a.LocationId = b.LocationId'''

I know the above SQL is messy, but I am new to INNER JOIN. I need the LocationId from table ChaplainLocation to replace LocationId in the User table.

Answer 1

Your current syntax is slightly off. Table aliases belong next to table names, and those appear in the FROM and JOIN clauses.

SELECT
    a.LocationID AS LocationID_a,
    a.ChaplainId AS ChaplainId_a,
    a.FullName,
    b.LocationId AS LocationId_b,
    b.ChaplainId AS ChaplainId_b
FROM [User] AS a
INNER JOIN [ChaplainLocation] AS b
    ON a.LocationId = b.LocationId;

Note that there is nothing wrong with referring to more than one column bearing the same name, so long as you qualify that column reference with a table alias (or full table name) to distinguish it.