Msg 209, Level 16, State 1, Line 4 Ambiguous column name 'DonorID'. Msg 209, Level 16, State 1, Line 1 Ambiguous column name 'DonorID'

Question

I'm new to this and the class is on line. I'm having a tough time trying to figure this out. Any help would be appreciated.

Thanks.

Select 
    LastName, FirstName, DonorID, FundName, TotalPledged
From 
    Donors.dbo.Donor, Donors.dbo.Pledge
Where   -- (marc_s): this was missing from your original query .....
    Donors.dbo.donor.donorID = Donors.dbo.Pledge.DonorID
    And DonorID = 59034;

In the `SELECT`, which `DonorID` do you want, the one from `Donor` table, or `Pledge` table? — Kingsley, Nov 02 '18 at 01:03
@Kingsley Answer: It doesn't matter, because of the join criteria. But you flagged a problem in my answer, thanks. — Tim Biegeleisen, Nov 02 '18 at 01:13
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 25 years** ago) and its use is discouraged — marc_s, Nov 02 '18 at 05:08

Tim Biegeleisen · Answer 1 · 2018-11-02T01:12:55.130

2

You are mixing implicit and explicit join syntax, and you should be using the latter:

SELECT LastName, FirstName, p.DonorID, FundName, TotalPledged
FROM Donors.dbo.Donor d
INNER JOIN Donors.dbo.Pledge p
    ON d.donorID = p.DonorID
WHERE d.DonorID = 59034;

You were missing a WHERE keyword, and also the ON keyword for explicit join.

edited Nov 02 '18 at 01:12

answered Nov 02 '18 at 00:58

Tim Biegeleisen

502,043
27
286
360

Is the explicit join really necessary in this case? I've seen a lot of this in SQL-Server SQL code, but rarely on say Oracle. – Kingsley Nov 02 '18 at 01:17
1

@Kingsley It's probably the better way to write the query. – Tim Biegeleisen Nov 02 '18 at 01:17

score 1 · Answer 2 · answered Nov 02 '18 at 03:46

The reason that you're getting an error is because the column DonorID appears in both of your tables, so the SQL engine doesn't know which value to display in the SELECT list, or which value to filter on in the WHERE clause.

The best practice, both to avoid this error and for readability, is to alias your table names and then apply those table aliases to every column name that appears anywhere in your query. It removes ambiguity for the SQL engine at run time, for you when you have to troubleshoot the query, for your buddy who has to cover the on-call rotation when it isn't you, and for the person who takes over your job when you move on.

As to your JOIN syntax, I strongly prefer the ANSI-92 explicit joins, but, oddly, there's still a religious war raging. Have a look at this pretty old post for arguments on both sides.

Msg 209, Level 16, State 1, Line 4 Ambiguous column name 'DonorID'. Msg 209, Level 16, State 1, Line 1 Ambiguous column name 'DonorID'

2 Answers2