SQL SELECT Duplicate Column

Question

I'm using this Query but I have it wrong...

SELECT * FROM [Orders]
JOIN [Customers]
ON [Orders].[CustomerID] = [Customers].[CustomerID]
WHERE [Orders].[OrderDate] BETWEEN '2010/1/1' AND '2011/1/1'
AND [Orders].[Total] > 1

I'm getting a Duplicate Column name error for CustomerID. I'm not sure how to use an Alias for this to work.

Could someone show me how to write it correctly.

EDIT:

Thanks for all the suggestions, here's what I can out with.

SELECT DISTINCT Orders.CustomerID, Orders.ShipToID, Orders.ShipName, Orders.ShipAddress, Orders.ShipAddress2, Orders.ShipCity, Orders.ShipStateOrProvince, Orders.ShipPostalCode, Orders.Total, Orders.OrderDate, Customers.Profession
FROM Orders
JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
WHERE Orders.OrderDate BETWEEN '4/3/2010' AND '2/20/2011'
AND Orders.Total > 1

Thanks Again!

Please don't use SELECT * in production code; name your columns. Please read sqlblog.org/blogs/aaron_bertrand/archive/2009/10/10/… — Aaron Bertrand
– Aaron Bertrand, Commented Feb 20, 2012 at 17:48
@AaronBertrand Great advice (and a very nice article, by the way). There's no indication in the post that this is production code though. Could just be testing =) — Josh Darnell
– Josh Darnell, Commented Feb 20, 2012 at 18:00
@jadarnel27 sure, but not using SELECT * still helps alleviate the issue - at least then you have control over aliases. — Aaron Bertrand
– Aaron Bertrand, Commented Feb 20, 2012 at 18:01
Also, I strongly recommend against using regional formats for date literals like 'yyyy/m/d' - I have an article for that too: sqlblog.org/blogs/aaron_bertrand/archive/2009/10/16/… — Aaron Bertrand
– Aaron Bertrand, Commented Feb 20, 2012 at 18:01
@AaronBertrand That's true, that would have avoided the problem altogether. — Josh Darnell
– Josh Darnell, Commented Feb 20, 2012 at 18:05

Klaus Byskov Pedersen · Accepted Answer · 2012-02-20 17:33:07Z

4

SELECT [Orders].* FROM [Orders] ...

To only get the columns from the Orders table. Or skip the * altogether and write explicitly which columns you want.

answered Feb 20, 2012 at 17:33

Klaus Byskov Pedersen

122k31 gold badges192 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michael Berkowski · Accepted Answer · 2012-02-20 17:33:25Z

3

Be explicit about the columns you're selecting, rather than SELECT *, and when you must get a similarly named column from both tables, use an alias.

SELECT
  Orders.OrderID,
  /* alias example, not that you'd need both CustomerID columns */
  Orders.CustomerID AS oCustomerID,
  Customer.CustomerID AS cCustomerID,
  Customers.Name,
  Customers.Address,
  ....
  ....
FROM [Orders]
JOIN [Customers]
ON [Orders].[CustomerID] = [Customers].[CustomerID]
WHERE [Orders].[OrderDate] BETWEEN '2010/1/1' AND '2011/1/1'
AND [Orders].[Total] > 1

answered Feb 20, 2012 at 17:33

Michael Berkowski

271k47 gold badges452 silver badges395 bronze badges

Comments

Josh Darnell · Accepted Answer · 2012-02-20 17:32:51Z

3

You need to specify a table at the beginning to avoid that error:

SELECT [Orders].* FROM [Orders]

If you do want every column from both tables, you will have to list them all out separately and alias the duplicate CustomerID columns.

SELECT 
    [Orders].CustomerID AS oCustID, 
    [Customers].CustomerID AS cCustID, 
    [Orders].anotherColumn,
    ...

answered Feb 20, 2012 at 17:32

Josh Darnell

11.5k9 gold badges40 silver badges66 bronze badges

Comments

Arion · Accepted Answer · 2012-02-20 17:42:37Z

2

You want to reference all the columns you want explicity rather than using *.

eg

SELECT 
   C.ID, 
   O.ID 
FROM 
   [Orders] O
   JOIN [Customers] C
     ON O.[CustomerID] = C.[CustomerID]
WHERE 
   O.[OrderDate] BETWEEN '2010/1/1' AND '2011/1/1'
   AND O.[Total] > 1

edited Feb 20, 2012 at 17:42

Arion

31.3k10 gold badges75 silver badges89 bronze badges

answered Feb 20, 2012 at 17:32

Sachin Kainth

47.1k87 gold badges212 silver badges309 bronze badges

1 Comment

Aaron Bertrand Over a year ago

I think you'll still want to give those columns aliases. Whatever is detecting duplicate columns is not going to care which table a column came from, it's still going to see two columns named ID in the resultset.

Collectives™ on Stack Overflow

SQL SELECT Duplicate Column

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related