SQL error Ambiguous column name

Question

I got this SQL

SELECT 
    COUNT_BIG (No_) AS [Antall ordre] 
FROM 
    dbo.[3S Company A_S$Warehouse Activity Header] 
INNER JOIN 
    dbo.[3S Company A_S$Sales Header] ON dbo.[3S Company A_S$Sales Header].[No_] = dbo.[3S Company A_S$Warehouse Activity Header].[Source No_]  
WHERE
    dbo.[3S Company A_S$Warehouse Activity Header].[Destination No_] = '" & strSelskab & "'  
    AND dbo.[3S Company A_S$Warehouse Activity Header].[No_ Printed] > 0

I get this error message

Ambiguous column name 'No_'.

What does that mean?

It means you have the same column name in more than one table, and you need to specify both the table name and column name in that case. Googling the exact error message should have given you plenty of information. — LittleBobbyTables - Au Revoir, Mar 26 '15 at 14:22
[Always provide proper table references for every column](http://blogs.sqlsentry.com/aaronbertrand/best-practices-referencing-columns/). — Aaron Bertrand, Mar 26 '15 at 14:43

score 0 · Accepted Answer · answered Mar 26 '15 at 14:26

0

There probably isn't any reason to count a column name. Just count all the rows using *:

SELECT COUNT_BIG(*) AS [Antall ordre] 
FROM dbo.[3S Company A_S$Warehouse Activity Header] ah NNER JOIN
     dbo.[3S Company A_S$Sales Header] sh
     ON sh.[No_] = ah.[Source No_]  
WHERE ah.[Destination No_] = '" & strSelskab & "'  and
      ah.[No_ Printed] > 0

Notice the use of table aliases. This also makes the query easier to write and to read.

answered Mar 26 '15 at 14:26

Gordon Linoff

1,242,037
58
646
786

While probably right, it *could* have been the intention that a nullable column in the right table was to be counted, which wouldn't necessarily yield the same count as *. – Aaron Bertrand Mar 26 '15 at 14:53

score 0 · Answer 2 · answered Mar 26 '15 at 14:27

0

Try to alias each table and then select by "alias.No_".

answered Mar 26 '15 at 14:27

Lourenci

123
8

SQL error Ambiguous column name

2 Answers2