CODEWITHSUNDEEP
c#.netsqlsql-serverdatabase
Dot NET
Dot NET

Reputation: 4897

Issue involving columns with the same name

I'm trying to create a view in C# involving two separate tables, where one field has the same name in both tables. Changing the column names is not possible, nor is creating new names for the columns just for the view. This is because the data cannot be modified in any way. I have been presented with the following error:

Column names in each view or function must be unique.
Column name 'Child_ID' in view or function 'Testing' is specified more than once.

An online search has revealed that this is because all columns must be unique. However, when I tried adding the respective table name before each offending column, the error was still returned. Is there any way to get around this? I simply cannot change the column names.

Upvotes: 0

Views: 900

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280340

Well, if Child_ID has the same value in both tables (I assume this is from a join), then you simply leave one of them out. What is the point of returning both?

If Child_ID has different values, then first off I question whether they should be called the same thing, but assuming there is a reason for this, use an alias.

SELECT 
  Child_ID_FromTable1 = Table1.Child_ID, 
  Child_ID_FromTable2 = Table2.Child_ID
...

Your query builder needs to recognize the case when multiple tables have the same column name, and either:

  1. automatically assign aliases in some pre-determined way (as I showed above, this wouldn't be difficult to do);
  2. force the user to select an alias for one or both columns; or,
  3. force the user to pick one or the other to remove from the output list.

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460158

Why don't you use column Aliases?

SELECT T1.Child_ID AS T1ChildID, T2.Child_ID AS T2ChildID,...

Upvotes: 2

Related Questions