CODEWITHSUNDEEP
sqlsql-server-2016
Bopinko
Bopinko

Reputation: 15

Error about "Ambiguous column name"

I just have an example for query in SQL Server 2016: write SQL code which will show values InvoiceNumber from table INVOICE which includes a value..............

I need to join 3 tables. This is my code:

SELECT 
    InvoiceNumber
FROM 
    INVOICE AS I 
INNER JOIN 
    LINE_ITEM AS LI ON (I.InvoiceNumber = LI.InvoiceNumber)
INNER JOIN 
    PRODUCT AS P ON (LI.ProductNumber = P.ProductNumber)
WHERE 
    Description = 'Heather Sweeney Seminar Live in Dakkas on 25-OCT-09 - Video'

and this code always results in an error :

Ambiguous column name InvoiceNumber

on the WHERE clause in the SELECT query

Upvotes: 2

Views: 5404

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Always qualify all your columns names in a query, especially if it has more than one table:

SELECT I.InvoiceNumber
FROM INVOICE AS I INNER JOIN
     LINE_ITEM AS LI
     ON I.InvoiceNumber = LI.InvoiceNumber INNER JOIN
     PRODUCT P
     ON LI.ProductNumber = P.ProductNumber
WHERE P.Description = 'Heather Sweeney Seminar Live in Dakkas on 25-OCT-09 - Video'

If you get into this habit, you simply will not ever have such an error.

Upvotes: 2

juergen d
juergen d

Reputation: 204766

If 2 tables have the same columns then you have to tell in the query which column you want to use. Do that by adding the table name in front of it.

SELECT I.InvoiceNumber FROM INVOICE AS I ...

Upvotes: 1

Related Questions