Columns missing table reference

Not referencing a table in the query is a common problem when writing queries, then extending the columns in a table at a later time.

Lets say you have two tables:

Table1 [People] has 3 columns, id, name and age.

Table3 [Phone] has 3 columns PeopleId, Number, Type

You write a query something like this

SELECT Number
  FROM [People]
 INNER JOIN [Phone] ON id = PeopleID
 WHERE id = 7;

At a later time a column id is added to the phone column. At that point you end up with two columns in the query that have the name of id. When the query was originally written it worked fine, but now you end up with an ambiguous reference in the WHERE, in the JOIN, and in the SELECT, it may or may not be referencing the correct id column.

Instead if you were to write it like this, the query would not be ambiguous, and it would work fine when future columns are added.

SELECT [Phone].Number
  FROM [People]
 INNER JOIN [Phone] ON [People].id = [Phone].PeopleID
 WHERE [People].id = 7;

Another option is to alias the tables like this:

SELECT ph.Number
  FROM [People] as p
 INNER JOIN [Phone] as ph ON p.id = ph.PeopleID
 WHERE p.id = 7;

Either of the last 2 examples will be more robust as the database changes or evolves.

Enroll Today!

---

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!

Download this page in PDF format