How to query twice at once from one table with inner join SQL ASP.NET-CodePudding

I have two tables first table called TFile contains two columns: FromCity and ToCity. They will have different values but from one column of the second table (TCity) and specifically from the column called CityName.Second table name TCity they have two column : IdCity AND CityName.

My problem I need to display data for two columns they got from second table FromCity and ToCity with inner join for two times.

I use this code to do that:

SqlCommand comm = new SqlCommand("select * from TFile "  
                            "inner join TCity AS A ON TFile.FromCity = A.IdCity "  
                                   "inner join TCity  AS B  ON TFile.ToCity  = B.IdCity "   " WHERE "   "TFile.Name", con);

Then display data to users as:

SqlDataReader srd = comm.ExecuteReader();


                if (srd.HasRows)
                {
                  
                    while (srd.Read())
                    {
                   
                       
    //FromCity
  TextFrom.Text = srd["CityName"].ToString();


//ToCity
 TextTo.Text = srd["CityName"].ToString();//=======================here problem

}

}

In the first line of the data display I can get the name of the city but if I repeat that in the second line it will just repeat the data. Here problem.I can't use a different name to access the second query instead of the field name CityName.This is the name of the field in the second table for which I display the names of the cities.

How can I access to data in this query:

"inner join TCity  AS B  ON TFile.ToCity  = B.IdCity

So if I access to it then can display second data in this line:

TextTo.Text = srd["CityName"].ToString();

How can solve this problem ?

CodePudding user response：

I bet you need to create field aliases to differentiate between values from the multiple joins on the same table. By the way, please make sure you protect yourself from "SQL injection" when using these types of queries. If you don't know what "SQL Injection" is then please take some time to look it up.

"select FromCityName=A.CityName, ToCityName=B.CityName, * from TFile "

And

//FromCity
TextFrom.Text = srd["FromCityName"].ToString();

//ToCity
TextTo.Text = srd["ToCityName"].ToString();//=======================here problem