Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Differentiating between 2 SQL column names with the same name in a C# SqlConnection

I have joined the same 2 tables twice using different aliases in a SQL query to enable me to select 2 (potentially but not always) different address ids which then link in to the address table.

SELECT C.court_id, C.court_name, CA.court_address, CA2.court_address...
FROM court C " +
JOIN court_addr CA ON C.court_addr_id = CA.court_addr_id " +
JOIN court_addr CA2 ON C.court_postal_addr_id = CA2.court_addr_id " + ...

Now when trying to output the results of this query using ASP.NET C# I'm unsure how to specify which of the two addresses to Response.Write. Putting the alias in front of the column name (as in the 4th string value below) doesn't work and brings up an error. Is there a way of differentiating between the two addresses in C# despite them both having the same column name in the database?

while (myDataReader.Read())
{
    string court_id = myDataReader["court_id"].ToString();
    string court_name = myDataReader["court_name"].ToString();
    string court_address = myDataReader["court_address"].ToString();
    string court_postal_address = myDataReader["CA2.court_address"].ToString(); 
    etc.....

Thanking you muchly in advance

like image 616
Dan Solo Avatar asked Jan 02 '13 16:01

Dan Solo


People also ask

Can two columns have same name in SQL?

In a DATA step, the program data vector does not allow two variables to have the same name. SQL is different. The namespace for a query can have multiple instances of the same column name.

Can two database columns have the same name?

You cannot create two columns with exactly the same name.

Can different tables have same column name?

Summary. “Using the same column name in different tables with different data-types” in an SQL database is simply “an accident waiting to happen.” It is easily avoided. Don't do it and don't do anything to encourage it.

How can find same column name in different table in SQL Server?

SELECT * FROM INFORMATION_SCHEMA. COLUMNS WHERE COLUMN_NAME LIKE 'PREFIX%' ORDER BY TABLE_NAME; Query: SELECT * FROM INFORMATION_SCHEMA.


1 Answers

You should use an alias in your sql to distinguish them, then you will be able to return the correct value:

SELECT C.court_id, 
  C.court_name, 
  CA.court_address as CACourtAddress, 
  CA2.court_address as CA2CourtAddress
FROM court C " +
JOIN court_addr CA ON C.court_addr_id = CA.court_addr_id " +
JOIN court_addr CA2 ON C.court_postal_addr_id = CA2.court_addr_id " + ...
like image 127
Taryn Avatar answered Sep 22 '22 16:09

Taryn