CODEWITHSUNDEEP
sql
Apmartin
Apmartin

Reputation: 85

how do I avoid the Ambiguous Column error in SQL?

From the code below, I am trying to merge 3 tables together. In one of these tables, it contains the year at which each project is completed and its project number. I am trying to join the tables but only contain the most up to date version of each project. Currently I am having the issue of the column "JobNO" being ambiguous and also there is an error with "YearMonth" which is the column that contains the dates. What is the best solution for this problem?

SELECT  LoadId.JobNo, LoadId.YearMonth, LoadId.Phase, JobCost.Depth, 
    JobCost.UOM, JobCost.CostCode, JobCost.CostCodeDesc, CostCat.Category, 
    CostCat.CurrentBudget,




FROM  CostCat INNER JOIN
      JobCost ON CostCat.JobCostID = JobCost.ID INNER JOIN  
      LoadId ON JobCost.LoadIdID = LoadId.ID INNER JOIN
      (SELECT JobNo, MAX(YearMonth) As MaxDate  FROM LoadId GROUP BY JobNo)
      LoadId2 ON LoadId.JobNo = LoadId2.JobNo AND LoadId.YearMonth = LoadId2.MaxDate  


GROUP BY JobNo, YearMonth, Phase, Depth, UOM, JobCost.CostCode, CostCodeDesc, Category, CostCat.CurrentBudget

Upvotes: 0

Views: 400

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

how do I avoid the Ambiguous Column error in SQL?

The answer is simple. You qualify all column references in the query. That includes the GROUP BY clause and subqueries.

Upvotes: 1

Related Questions