CODEWITHSUNDEEP
sql-serverjoinambiguous
gmt
gmt

Reputation: 93

SQL Server: Join with ambiguous name columns

I have two tables in SQL Server (I removed many rows, but it should not affect the result):

[dbo].[Order]:

CaseId    Gender
-----------------
698       Female
694       Male
676       Female
659       Male

[dbo].[Plimplan]:

OrderID     CaseID            InfoID              
--------------------------------------------
33425999      698             BBBBBE           
33425984      694             AAAAAS           
33425984      694             DSSSAS             
33425609      694             BBBBBA           
33425270      676             AAAAAA           
33424973      676             AAAAAD           
33424716      676             SASSAS           
33424704      676             AAAAAA           
33424500      676             BBBBBE           
33424340      659             AAAAAA           
33424281      659             BBBBBD

and I want to obtain the following one:

[dbo].[Plimplan]:

OrderID     CaseID            InfoID          Gender      
----------------------------------------------------
33425984      694             AAAAAS           Male
33425270      676             AAAAAA           Female
33424340      659             AAAAAA           Male

Let's forget the join. I can remove undesired rows from [dbo].[Order] with this:

 SELECT TOP (100) MAX (OrderID) AS [OrderID]
  ,MAX (CaseID) AS [CaseID]
  ,MAX (InfoID) AS [InfoID] 
  /*,[Gender]*/

  FROM [dbo].[Plimplan] as Plimpl
  /* INNER JOIN [dbo].[Order] as Ord
  ON Plimpl.CaseID = Ord.CaseID */

  WHERE [InfoID] NOT LIKE 'BBBBB%'
  and CaseID < 700
  and CaseID > 600
  GROUP BY (CaseID)
  order by CaseID desc

But if I remove the comments to complete the join, I get this error:

Msg 209, Level 16, State 1, Line 3 Ambiguous column name 'CaseID'.

Upvotes: 0

Views: 196

Answers (2)

Mazhar
Mazhar

Reputation: 3837

You're getting the error when you uncomment the JOIN statement right?

Use Aliases

 SELECT TOP (100) MAX (OrderID) AS [OrderID]
  ,MAX (CaseID) AS [CaseID]
  ,MAX (InfoID) AS [InfoID] 
  /*,[Gender]*/

  FROM [dbo].[Plimplan] as Plimpl
   INNER JOIN [dbo].[Order] as Ord
  ON Plimpl.CaseID = Ord.CaseID 

  WHERE [InfoID] NOT LIKE 'BBBBB%'
  and Plimpl.CaseID < 700
  and Plimpl.CaseID > 600
  GROUP BY Plimpl.CaseID
  order by Plimpl.CaseID desc

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521168

Use aliases. The error you are getting is due to both tables in the join having a CaseID column. SQL Server is telling you that it can't figure out which one you are trying to select.

SELECT TOP 100
    MAX (Plimpl.OrderID) AS [OrderID]
    MAX (Plimpl.CaseID)  AS [CaseID]
    MAX (Plimpl.InfoID)  AS [InfoID] 
FROM [dbo].[Plimplan] AS Plimpl
INNER JOIN [dbo].[Order] AS Ord
     ON Plimpl.CaseID = Ord.CaseID
WHERE
    Plimpl.[InfoID] NOT LIKE 'BBBBB%' AND
    Plimpl.CaseID < 700 AND
    Plimpl.CaseID > 600
GROUP BY
    Plimpl.CaseID
ORDER BY
    Plimpl.CaseID DESC;

You had at one point apparently been selecting the Gender column from the Order table, but it makes no sense to do this with your use of GROUP BY.

Upvotes: 3

Related Questions