none
get data from sql server database in two inner join tables which has a same column name using c# RRS feed

  • Question

  • now i use c# to get all the data from a sql server database.

    the sql command is a inner join query, table_1 inner join table_2. like the following:

    sqlcmd = "select table_1.*, table_2.* from table_1 inner join table_2 on table_1.id = table_2.id "

    there is a same column name temp_id in both the table_1 and table_2.

    and  then i want to only get the temp_id in table_1 from the resulted datatable (like use xx.Rows[0]["temp_id"]), how to do it?

    thanks

    Friday, November 10, 2017 5:47 AM

All replies

  • Change the select statement to "select table_1.*, " then spell all the field names on table_2 in "table_2.fieldname" format and skip "temp_id" there.
    Friday, November 10, 2017 6:45 AM
    Answerer
  • thanks.

    but i also need all other the data in my code.

    if used table_2.filedname, other data in table_2 will be lost. 

    Friday, November 10, 2017 7:41 AM
  • I mean, you have to write each and every field names you need in table2 in order to filter out field names that you don't want.

    There's no special syntax in SQL that allow you to opt out fields this way, since in both of your tables you have "id" and "temp_id" fields but you just want to use "id" to join. So "natural join" is not applicable.


    Btw, just like you shouldn't use "natural join" in production code (it's a future bug waiting to happen as you add fields - especially the audit and state control ones), you shouldn't use "*" in select statement of production code. Always explicitly line out the fields one by one. It can save you hours or even days of debugging time.
    Friday, November 10, 2017 7:42 AM
    Answerer
  • ok.

    thanks.

    Friday, November 10, 2017 7:58 AM
  • One more suggestion ... try to use Stored Procedures instead of writing your queries in your C# code, for a couple of reasons: 

    1. Stored Procedure calls are optimized by SQL Server much more so than on-the-fly queries.
    2. If your query needs to change just a little bit, then if your query is in your C# code, you'll have to recompile your application. But, if you used a Stored Procedure, everything gets changed in SQL and no recompilation of your code is necessary (unless, of course, the change in the query affects how your data is processed in your code).

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, November 12, 2017 1:41 AM
    Moderator

  • Hi  imeya,

    I am glad to know you solved your issue. It would be appreciated if you could close the thread by marking helpful posts as an answer. This will help other members to find the solution quickly if they have faced the similar issue.

    Best Regards,

    Yohann Lu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 13, 2017 5:03 AM
    Moderator