MySQL Parameterized Select Query joining tables issue

Question

I am using parameterized select queries in conjunction with my program and as a whole, it works except when I try to join tables.

Here is a small excerpt what I have done:

using (MySqlCommand cmd = new MySqlCommand(paramQuery.ToSql(), connection)) {

/*
   paramQuery.ToSql() equals:

   "Select tableOne.ID, tableOne.Department, tableTwo.Name
   From tableOne, tableTwo
   Where tableOne.ID = @param0"
*/


    for (int index = 0; index < paramQuery.Parameters().Count; index++) 
        cmd.Parameters.AddWithValue(paramQuery.Parameters().ElementAt(index).Key,
            paramQuery.Parameters().ElementAt(index).Value);

    /*
       paramQuery.Parameters().ElementAt(index).Key = "@param0"
       paramQuery.Parameters().ElementAt(index).Value = "tableTwo.ID"
    */

    MySqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read()) {
            // Do stuff
    }

}

One of the tables that I am try to join will retrieve everyone in tableTwo with the same ID for all. Is there something that I am doing wrong here?

All Blond · Accepted Answer · 2014-04-28 15:25:19Z

1

try this:

  Select tableOne.ID, tableOne.Department, tableTwo.Name
  From tableOne join tableTwo
    on tableOne.ID=tableTwo.ID
  Where tableOne.ID = @param0

as you understand tableOne.ID=tableTwo.ID is the name of the columns which you joining tables on. At your current example I do not see how it can work. There is also Left join and right join which serves each to a different purposes. Here is some example to look how it works: http://dev.mysql.com/doc/refman/5.0/en/join.html

edited Apr 28, 2014 at 15:25

answered Apr 28, 2014 at 15:20

All Blond

8221 gold badge8 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Hayden Over a year ago

Using the sql query in MySql has been successful but I will try your solution and report back.

All Blond Over a year ago

Keep in mind that Join syntax also greatly depends on Version of MySQL you are using.Your example would be valid in MySQL 4.0 or earlier but in 5 and later they seems to decide to make it more SQL Server like.

Hayden Over a year ago

I tried using my original statement in MySQL and it worked but following your advice, I used a proper join and it now works.

Collectives™ on Stack Overflow

MySQL Parameterized Select Query joining tables issue

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related