Select data from multiple tables without join in sql, ado.net

Its very easy to get data from more than one table without using the join. This type of query we mostly used in the Ado.net. Where we type queries to get data from the database. I am going to explain how to get data from two tables in ado.net without join query.

suppose we have two tables table1, table2, each has one common field which is ID.

So get data from two tables we can use below query in ado.net

SELECT table1.Empname, table2.Address from table1, table2 where table1.ID='123' AND table2.ID='123';

you can check ado.net code example below where i am getting data from three tables without join.

var _orderId = Request.QueryString["OrderId"];
 using (iDB2Connection conn = new iDB2Connection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
                {
                    string sqlStatement = "SELECT CUSTMAST.CUNAME, ORDER.ORNAME, COMMENT.CODESC FROM CUSTMAST, COMMENT, ORDER  WHERE CUCODE IN (SELECT ORDER.ORCONS FROM ORDER where UPPER(ORODR#)=@orderid) AND UPPER(COMMENT.OCORD#)=@orderid2 AND UPPER(ORDER.ORODR#)=@orderid3";
                    iDB2Command cmd = new iDB2Command(sqlStatement, conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@orderid", _orderId.ToUpper());
                    cmd.Parameters.Add("@orderid2", _orderId.ToUpper());
                    cmd.Parameters.Add("@orderid3", _orderId.ToUpper());
 try
                    {
                        conn.Open();
                        iDB2DataReader dr = cmd.ExecuteReader();
                        if (dr.HasRows)
                        {
                            while (dr.Read())
                            {
                                lblCustName.Text = dr.GetString(0); 
                                lblOrderName.Text = dr.GetString(1);
                                lblCommentDesc.Text = dr.GetString(2); 
else
                        {
                            Console.WriteLine("No record found.");
                        }
                        //Close Outer data reader
                        dr.Close();
                    }
                    catch (Exception ex)
                    {
                        Response.Write("Error" + ex.Message);
                    }
                    finally
                    {
                        conn.Close();
                    }

Hope this will help you guys.
Happy Coding… Cheers

Posted by | View Post | View Group