locked
MYSQL: how to select data from two tables without join statement !! RRS feed

  • Question

  • User-1123701243 posted

    Hi all 
    I need to join data from two tables but without using left join statement. 
    Ex:

    select t1.*,t2.* from t1,t2 where t1.id=t2.id;


    The above statement shows only matched records from both tables. 
    BUT I need to select ALL records from t1 and matched records from t2. 
    For ex if t1 has 10 records and t2 matches t1 in 4 records only, then the result must show 10 records. 

    I don't want to use (LEFT JOIN), because I noticed that it takes longer time than using (WHERE) clause. 
    In oracle I could use: where t1.id(+)=t2.id 
    Ex: 

    select t1.*,t2.* from t1,t2 where t1.id(+)=t2.id; 

    thanks.

    Tuesday, April 15, 2014 11:22 AM

All replies

  • User-192709268 posted
    Hi
    
    try this
    
    // Query1 
    string sql1 = @"select ID,Name from Person  ";
     
    //Query2 
    string sql2 = @"select FirstName, LastName from Person"; 
    
    string sql = sql1 + sql2; 
    
    // Create connection 
    MySqlConnection conn = new MySqlConnection(connString); 
    try 
    { 
    // Open connection 
    conn.Open(); 
    // Create command 
    MySqlCommand cmd = new MySqlCommand(sql, conn); 
    cmd.ExecuteScalar(); 
    }
    catch (MySqlException ex)
    {
        ShowMessage(ex.Message);
    }
    Finally
    {
       conn.Close();
    }

    Wednesday, April 16, 2014 6:43 AM
  • User-271186128 posted

    Hi human2x,

    As for this issue, I suppose we can achieve it using following steps.

    1. Create a temporary table (include table 1 and table 2 columns).
    2. Query the matched records from table 1 and table2, and insert them into the temporary table.
    3. Query the not matched records from table 1, and insert it into the temporary table.
    4. Query the temporary table. Get the required value.

    Best Regards,
    Dillion

    Thursday, January 29, 2015 5:25 AM