CODEWITHSUNDEEP
sqlt-sql
cxwilson
cxwilson

Reputation: 107

SqL Left Outer Join Ambiguous Column Name

I am getting an error saying that GeographyName is ambiguous but I'm not sure what this means:

SELECT
    ROW_NUMBER() OVER (ORDER BY GeographyName ASC) AS RowID,
    g.GeographyName,
    c.categorydesc AS CategoryDesc,
    ROUND(SUM(s.TotalFunding), 0) AS TotalFunding, g.GeographyCode,
    t.GeographyTypeDesc,
    r.GeographyRegionDesc,
    g.GeographyRegionID,
    a.Latitude,
    a.Longitude
FROM dbo.StateSummaryByCategory AS s
INNER JOIN dbo.Geography AS g
    ON g.GeographyID = s.GeographyId
INNER JOIN dbo.GeographyType AS t
    ON g.GeographyTypeID = t .GeographyTypeID
INNER JOIN dbo.GeographyRegion AS r
    ON g.GeographyRegionID = r.GeographyRegionID
INNER JOIN dbo.Category AS c
    ON s.categoryid = c.categoryid
LEFT OUTER JOIN dbo.Geography_LatLons a
    ON g.GeographyCode = a.GeoCode AND
       g.GeographyName = a.GeographyName

Upvotes: 0

Views: 2375

Answers (4)

Nitin Patel
Nitin Patel

Reputation: 1651

"GeographyName is ambiguous" means as result data there are two GeographyName column name. So, when you trying "ORDER BY GeographyName" in SQL, SQL get ambiguity error that in simple, it cannot choose which one should consider for ordering. Therefore, solution is to put "ORDER BY g.GeographyName" or a.GeographyName as per your requirements in instead of 'ORDER BY GeographyName' your statement.

Upvotes: 1

Sankar
Sankar

Reputation: 7107

in your order by on RowID column.

SELECT ROW_NUMBER() OVER (
                          ORDER BY g.GeographyName ASC) AS RowID,
       g.GeographyName,
       c.categorydesc AS CategoryDesc,
       ROUND(SUM(s.TotalFunding), 0) AS TotalFunding,
       g.GeographyCode,
       t .GeographyTypeDesc,
       r.GeographyRegionDesc,
       g.GeographyRegionID,
       a.Latitude,
       a.Longitude
FROM dbo.StateSummaryByCategory AS s
INNER JOIN dbo.Geography AS g ON g.GeographyID = s.GeographyId
INNER JOIN dbo.GeographyType AS t ON g.GeographyTypeID = t .GeographyTypeID
INNER JOIN dbo.GeographyRegion AS r ON g.GeographyRegionID = r.GeographyRegionID
INNER JOIN dbo.Category AS c ON s.categoryid = c.categoryid
LEFT OUTER JOIN dbo.Geography_LatLons a ON g.GeographyCode = a.GeoCode
AND g.GeographyName = a.GeographyName

GeographyName field is found in more than one column. So you have mention which one you are going to be used to sort

Upvotes: 2

manthan
manthan

Reputation: 132

Make sure that you are using proper table alias name with every column mentioned in select, join and where statement.

Upvotes: 0

Arjan Einbu
Arjan Einbu

Reputation: 13672

Ambiguous Colum Name means that SQL Server has more than one column by a specific name to choos from, and doesnt know which one.

I see you have a lot of joins in your query, and thus bring in a lot of tables, maybe the same column name is used in more than one of these.

It seems your problem is in line 2 of your query, where you don't specify which table the GeographyName column is from.

Upvotes: 3

Related Questions