MySQL

[klaydze]

Hi All, Can someone explain why MySql treat the two sql statement below different? First Query (without inner join)

MySqlConnection conn = new MySqlConnection("server=localhost;database=db_test;uid=root;password=1234567");
MySqlCommand dbCommand = new MySqlCommand("select * from useraccess_privileges", conn);
dbCommand.CommandType = CommandType.Text;
if (dbConn.DBConnect.State == ConnectionState.Closed)
dbConn.DBConnect.Open();
DataTable dtData = new DataTable();
dtData.Load(dbCommand.ExecuteReader());

Result: dtData will return 7 row count

Second Query (with inner join)

MySqlConnection conn = new MySqlConnection("server=localhost;database=db_test;uid=root;password=1234567");
MySqlCommand dbCommand = new MySqlCommand("select t1.*, t2.MenuName from user_access t1 inner join useraccess_privileges t2 on t1.UserName = t2.UserName", conn);
dbCommand.CommandType = CommandType.Text;
if (dbConn.DBConnect.State == ConnectionState.Closed)
dbConn.DBConnect.Open();
DataTable dtData = new DataTable();
dtData.Load(dbCommand.ExecuteReader());

Result: dtData will return 1 row count

Second query should return 7 rows also since i just reference it to other table to get the full name of the specific username. Thanks

if(you type your code here) { Messagebox.Show("You help me a lot!"); } else { You help me = null; }

[Bernhard Hiller]

Because user_access contains only one row matching useraccess_privileges. useraccess_privileges has 7 rows, user_access has 1 or more rows. Try a LEFT JOIN instead:

SELECT useraccess_privileges.MenuName, user_access.*
FROM useraccess_privileges
LEFT JOIN user_access
ON user_access.UserName=useraccess_privileges.UserName

and you'll get again 7 rows.

[klaydze]

Hi Bernhard, Yep, i already did that but no luck. I don't understand why it return only 1 row. The work around that I did was like this.

DataTable dt = new DataTable();
MySqlCommand command = new MySqlCommand();
command.CommandType = CommandType.Text;
command.CommandText = "SELECT useraccess_privileges.MenuName, user_access.* FROM useraccess_privileges LEFT JOIN user_access ON user_access.UserName=useraccess_privileges.UserName";

MySqlDataAdapter da = new MySqlDataAdapter(command);
da.SelectCommand.Connection = conn;
da.Fill(dt);

If the above code was applied then it will return the correct row number. I really don't understand why. Thanks

if(you type your code here) { Messagebox.Show("You help me a lot!"); } else { You help me = null; }