Transact-SQL Ambiguous column name

问题:

I'm having trouble with the 'Ambiguous column name' issue in Transact-SQL, using the Microsoft SQL 2012 Server Management Studio.

I´ve been looking through some of the answers already posted on Stackoverflow, but they don´t seem to work for me, and parts of it I simply don´t understand or loses the general view of.

Executing the following script :

USE CDD

SELECT Artist, Album_title, track_title, track_number, Release_Year, EAN_code
FROM   Artists AS a INNER JOIN CD_Albumtitles AS c 
    ON     a.artist_id = c.artist_id
INNER JOIN Track_lists AS t 
    ON     c.title_id = t.title_id
WHERE track_title = 'bohemian rhapsody'

triggers the following error message :

Msg 209, Level 16, State 1, Line 3 Ambiguous column name 'EAN_code'.

Not that this is a CD database with artists names, album titles and track lists. Both the tables 'CD_Albumtitles' and 'Track_lists' have a column, with identical EAN codes. The EAN code is an important internationel code used to uniquely identify CD albums, which is why I would like to keep using it.

回答1:

You need to put the alias in front of all the columns in your select list and your where clause. You're getting that error because one of the columns you have currently is coming from multiple tables in your join. If you alias the columns, it will essentially pick one or the other of the tables.

SELECT a.Artist,c.Album_title,t.track_title,t.track_number,c.Release_Year,t.EAN_code
FROM Artists AS a INNER JOIN CD_Albumtitles AS c
ON a.artist_id = c.artist_id
INNER JOIN Track_lists AS t
ON c.title_id = t.title_id
WHERE t.track_title = 'bohemian rhapsody'

回答2:

so choose one of the source tables, prefixing the field with the alias (or table name)

SELECT Artist,Album_title,track_title,track_number,Release_Year,
       c.EAN_code -- or t.EAN_code, which should retrieve the same value

By the way, try to prefix all the fields (in the select, the join, the group by, etc.), it's easier for maintenance.