2

I have two tables that I want to join and use to fill a data table, and I am having an issue with column name collision:

-----------    -----------
| Parent  |    | Child   |
-----------    -----------
| ParentID|    | ChildID |
| Name    |    | ParentID|
| Value   |    | Name    |
-----------    | Value   | 
               -----------

The SQL statement (MySQL database, in case it's relevant)

select p.*, c.* from parent p left join child c on c.ParentID = c.ChildID

My adapter code (C#):

var adapter = new MySqlDataAdapter(sql, DBConn);
adapter.Fill(table);

When I go to read the rows, i expect something like this:

var parentName = row["p.Name"];
var childName = row["c.Name"];

Instead, it is resulting in this:

var parentName = row["Name"];
var childName = row["Name1"];

How do i get the column names to use the aliases? I feel like I am missing something obvious, because this seems like it should come up all the time.

normanthesquid
  • 690
  • 1
  • 6
  • 21
  • Hey Norm, you should select an answer if we've helped you! :) upvote if you havent yet too. – crthompson Mar 07 '14 at 17:01
  • Yeah, Rafas answer solves this specific case, but there is a general use case that is doesn't. From below: It's not a matter of what I "have" to alias, its about removing special cases from the code. I'd rather alias everything than only some things. If i can't come up with anything better, his answer asks the question I asked, so I will accept it. – normanthesquid Mar 07 '14 at 17:10
  • I think aliasing everything is the right way to go. If you are generating the sql appending something like `"as " + tablename + "_" + columnname` to each column fixes all your issues and is very clear code. – crthompson Mar 07 '14 at 17:13
  • Also, @Rafas answer was the first one and so there is no reason he shouldnt get the answer. Its a great one and I upvoted him. Ostensibly however, we say the same thing. – crthompson Mar 07 '14 at 17:16
  • Agreed, I just wish there was a "select p.tablename_*" so i didnt have to explicitly name all the columns. I can use a generator for building the thing that builds the sql, though, which means I don't have to write it all out by hand, at least – normanthesquid Mar 07 '14 at 17:27
  • You should look into using an ORM like entity framework or Nhibernate. – crthompson Mar 07 '14 at 17:54
  • Oh man, I love entity framework, but that's an uphill battle. 3-4 years ago they had some idiot do a bad implementation of EF, and the performance was so bad EF is a bad work around here. I've only been here 4 months, but I'm trying to show that its not EF, but the implementation at fault, but i don't have cache to change out our entire DL just yet. Piece by piece, lol. – normanthesquid Mar 07 '14 at 18:00
  • I know that situation friend, keep at it. Proofs of concept and mini projects help turn the tide. Good luck. – crthompson Mar 07 '14 at 18:15

3 Answers3

4

Since name is a common column name for both tables, you have to give an alias in your query to any of them. If you do not provide one, it will provided automatically one for the repeated fields, in that case appending a "1".

In addition, c. and p. are only internal alias for your query but they not form part of the returning column names.

In your SQL query put the alias (at least one of them)

SELECT p.name as parent_name, c.name as child_name, ….

And in your C# code use the proper columns

var parentName = row["parent_name"];
var childName = row["child_name"];
Rafa Paez
  • 4,820
  • 18
  • 35
  • Is there any way to do a table wide prefix? the actual tables have many columns, and having to individually alias each column is cumbersome. – normanthesquid Mar 07 '14 at 14:52
  • You only have to alias those ones that have the same name. In addition, is not good idea to select using `.*` because this cause the database to do an extra query first (internally) to find the column names. – Rafa Paez Mar 07 '14 at 15:17
  • It's not a matter of what I "have" to alias, its about removing special cases from the code. If I can pass a table wide prefix in to the select (like select p.Parent_*, I know this isn't a thing) then I can make that prefix a variable that can be usable both when writing the sequel and when processing the rows. My model has many tables, and having to write special cases every time a column name collision exists is unattractive, compared to a universal solution. I'd rather alias everything than only some things. – normanthesquid Mar 07 '14 at 16:08
1

Whenever you are dealing w/ sql strings its always a good convention to write out all your field names instead of using *

In this case your problem is that you have name in your query twice, so its auto populating a 1 after it to disambiguate.

Try this instead:

select
    p.ParentId,
    p.Name as parent_name,
    p.Value as parent_value
    c.ChildId,
    c.Name as child_name,
    c.Value as child_value
from parent p left join child c on p.ParentID = c.ChildID

Your c# code would need to reflect this as well.

var parentName = row["parent_name"];
var childName = row["child_name"];
crthompson
  • 15,653
  • 6
  • 58
  • 80
  • Just a typo issue - you have `c.ParentID = c.ChildID` it should be `p.parentId = c.childId` – Evan L Mar 06 '14 at 23:18
  • @EvanL Thanks, OP has the column names all capitalized, and mine was lower case. Should be correct now. – crthompson Mar 06 '14 at 23:31
  • Hmmm... i don't think you can do a join on two columns from the same table. He has `parent p left join child c on c.parentId = c.childId` and so do you. It really should be `p.parentId`. Just nitpicking really, I still +1'd you ;) – Evan L Mar 06 '14 at 23:34
  • Good eyes? No... Too much SQL on a day to day basis? Yes ;) – Evan L Mar 06 '14 at 23:35
0

The aliases in the query are for mysql to know what you mean, if you want unique column names in the output

Select p.Name as ParentName, c.Name as ChildName .... etc

I certainly wouldn't rely on the Name1 drivel, change your query to select c.*, p.* and things will go mental.

Tony Hopkinson
  • 20,172
  • 3
  • 31
  • 39