CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Phil
Phil

Reputation: 43

Ambiguous column name when doing a JOIN

I've got some troubles when doing a join in my statement

This one is OK

select [db_id], COUNT(db_id) AS total
FROM [dbauditor_repo].[dbo].[dbauditor_repo_events]
GROUP BY [db_id]
ORDER BY total DESC

But I'm getting an error when doing the join (Ambiguous column name 'db_id'.)

SELECT [db_name], [db_id], COUNT(db_id) AS total
FROM [dbauditor_repo].[dbo].[dbauditor_repo_events] JOIN [dbauditor_repo].[dbo].[dbauditor_repo_warehouse]
ON ([dbauditor_repo].[dbo].[dbauditor_repo_events].[db_id] = [dbauditor_repo].[dbo].[dbauditor_repo_warehouse].[db_id])
WHERE [db_type] = 'mysql'
GROUP BY [db_id]
ORDER BY total DESC

Any idea ?

Upvotes: 1

Views: 5670

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Just get in the habit of using table aliases for all column references. That will prevent such errors and other unexpected problems. The best practice is to use table abbreviations. Here is an example:

SELECT rw.[db_name], re.[db_id], COUNT(re.db_id) AS total
FROM [dbauditor_repo].[dbo].[dbauditor_repo_events] re JOIN
     [dbauditor_repo].[dbo].[dbauditor_repo_warehouse] rw
     ON re.[db_id] = rw.[db_id])
WHERE rw.[db_type] = 'mysql'
GROUP BY rw.[db_name], re.[db_id]
ORDER BY total DESC;

Of course, I have to guess which tables the columns come from. You should fix the query so they come from the right tables.

As a bonus, when using table aliases, queries are easier to write and to read.

Upvotes: 5

Dimt
Dimt

Reputation: 2328

You need to use fully qualified name when joining tables and the column exist in more than one table.

Try:

SELECT [dbauditor_repo_events].[db_id]

You also need to include the column db_name in the GROUP BY clause as this column is not being aggregated.

GROUP BY [dbauditor_repo_events].[db_id], [db_name]

Upvotes: 3

ngrashia
ngrashia

Reputation: 9904

Problem:

It is because:

[dbauditor_repo].[dbo].[dbauditor_repo_events]

and

[dbauditor_repo].[dbo].[dbauditor_repo_warehouse]

have the same column name db_id. The Query does not know which table's db_id to be used. Hence this error.

Solution:

Use TableName.FieldName or TableAliasName.FieldName to avoid this error. Change your Query as:

SELECT [db_name], 
       TAB1.[db_id] , 
       COUNT(TAB1.db_id) AS total
FROM 
    [dbauditor_repo].[dbo].[dbauditor_repo_events] AS TAB1
JOIN
    [dbauditor_repo].[dbo].[dbauditor_repo_warehouse] AS TAB2
ON 
    (TAB1.[db_id] = TAB2.[db_id])
WHERE [db_type] = 'mysql'

GROUP BY [db_name],[TAB1.db_id]

ORDER BY total DESC

Upvotes: 1

Paddy
Paddy

Reputation: 33857

It's in your select and group by:

SELECT [db_name], [dbauditor_repo_events].[db_id], COUNT([dbauditor_repo_events].db_id) AS total
FROM [dbauditor_repo].[dbo].[dbauditor_repo_events] JOIN [dbauditor_repo].[dbo].[dbauditor_repo_warehouse]
ON ([dbauditor_repo].[dbo].[dbauditor_repo_events].[db_id] = [dbauditor_repo].[dbo].[dbauditor_repo_warehouse].[db_id])
WHERE [db_type] = 'mysql'
GROUP BY [dbauditor_repo_events].[db_id]
ORDER BY total DESC

You should maybe consider aliasing your tables in the query for readability,e.g.:

SELECT [db_name], e.[db_id], COUNT(e.db_id) AS total
FROM [dbauditor_repo].[dbo].[dbauditor_repo_events] as e
JOIN [dbauditor_repo].[dbo].[dbauditor_repo_warehouse] as w
ON (e.[db_id] = w.[db_id])
WHERE [db_type] = 'mysql'
GROUP BY e.[db_id]
ORDER BY total DESC

Upvotes: 1

Related Questions