14

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

Dan Solo
  • 697
  • 1
  • 8
  • 21

4 Answers4

32

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 " + ...
Taryn
  • 242,637
  • 56
  • 362
  • 405
  • Excellent, thanks. And especially for the very fast reply. Worked a treat and I can use the same principle on other columns that may need to appear more than once. – Dan Solo Jan 02 '13 at 16:22
  • @DanSolo - Using a meaningful alias should help make life easier. For example, if the first `JOIN` is to locate the Court Of Appeals (IANAL.) then use `CourtOfAppeals` or `CoA` rather than `CA2` for the alias. I generally use a dot in column aliases to separate the table alias from the column name, e.g. `CoA.Court_Name`. – HABO Jan 02 '13 at 17:00
3

You should use alias name to distinguish two columns having same name like :

SELECT C.court_id, C.court_name, CA.court_address CourtAddress, CA2.court_address CourtPostalAddress 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

And then in C# you can access them very easily :

 string court_id = myDataReader["court_id"].ToString();
 string court_name = myDataReader["court_name"].ToString();
 string court_address = myDataReader["CourtAddress"].ToString();
 string court_postal_address = myDataReader["CourtPostalAddress"].ToString();

I hope this helps solve your problem :)

JustinP
  • 1,351
  • 1
  • 14
  • 29
Sonu Kukreja
  • 31
  • 2
2

you could access the columns via index if you cannot modify the query.

var foo = MyDataReader[0].ToString();

Or you could modify the query using the AS keyword in your sql statement.

SELECT foo AS bar FROM Baz
Charles Lambert
  • 5,042
  • 26
  • 47
0

In addition to providing a column name alias as bluefeet suggests, another way would be to access the values by index instead of by name in C#. 

string theValue = myReader[0] as string ?? String.Empty;

This, however, requires the order of the columns to never change and is thus to be used carefully. Also, this only works as you specifically select the columns by name in the given order in your SQL statement.

This may not work for a SELECT *, as the order of the returned columns is not fixed in a SELECT *.

Thorsten Dittmar
  • 55,956
  • 8
  • 91
  • 139