How to get data from two table using one query

Posted Date: 17 Jul 2012 Posted By:: Rameshwar

Member Level: Silver Member Rank: 623 Points: 2 Responses: 5

In C#.net code I am using two table 1. Member ID 2. Member Name

Now I have to display the MemberID :--- Member Name :----- on form.

How I will join there two table using one query.?

Regards
Rameshwar

Responses

#680688 Author: PalaniKumar.A Member Level: Gold Member Rank: 266 Date: 17/Jul/2012 Rating: Points: 3
Dear Rameshwar, You can use join query for 2 Tables Sample Query: `select A.* from Table1 A Left outer join Table2 B on A.MemberID=B.MemberID_FK where A.MemberID is not null or select MemberID, Member Name from Table1 where MemberID =(select MemberID from Table2)` It will display the records to A table Palanikumar.A $-Success is often the result of taking a misstep in the right direction-$

#680689 Author: Manoj Savalia Member Level: Silver Member Rank: 776 Date: 17/Jul/2012 Rating: Points: 4
I have found out the way to get the join of the tables using the GetParentRow() method in combination with a "Relation" in the DataSet. Here is the code: DataTable dt1 = new DataTable("Table1"); DataTable dt2 = new DataTable("Table2"); DataSet ds = new DataSet("DataSet"); dt1.Columns.Add("MemberID", typeof(Int32)); dt1.PrimaryKey = new DataColumn[] { dt1.Columns["MemberID"] }; dt2.Columns.Add("MemberID", typeof(Int32)); dt2.Columns.Add("Membername", typeof(String)); dt2.PrimaryKey = new DataColumn[] { dt2.Columns["MemberID"] }; ds.Tables.Add(dt1); ds.Tables.Add(dt2); // Loading data into dt1, dt2: object[] o1 ={ 1 }; object[] o2 ={ 2 }; object[] o3 ={ 3 }; object[] c1 ={ 1, "MFG" }; object[] c2 ={ 2, "EAS" }; object[] c3 ={ 3, "E&U" }; dt2.Rows.Add(c1); dt2.Rows.Add(c2); dt2.Rows.Add(c3); dt1.Rows.Add(o1); dt1.Rows.Add(o2); dt1.Rows.Add(o3); DataRelation drel = new DataRelation("EquiJoin", dt2.Columns["MemberID"], dt1.Columns["MemberID"]); ds.Relations.Add(drel); DataTable jt = new DataTable("Joinedtable"); jt.Columns.Add("MemberID", typeof(Int32)); jt.Columns.Add("Membername", typeof(String)); ds.Tables.Add(jt); foreach (DataRow dr in ds.Tables["Table1"].Rows) { DataRow parent = dr.GetParentRow("EquiJoin"); DataRow current = jt.NewRow(); // Just add all the columns' data in "dr" to the New table. for (int i = 0; i < ds.Tables["Table1"].Columns.Count; i++) { current[ i ] = dr[ i ]; } // Add the column that is not present in the child, which is present in the parent. current["MemberID"] = parent["MemberID"]; jt.Rows.Add(current); } //Bind your textboxs with ds.Tables["Joinedtable"]; txtMemberID.text = ds.Tables["Joinedtable"].Rows[0]["MemberID"].toString(); txtMemberName.text = ds.Tables["Joinedtable"].Rows[0]["Membername"].toString(); Finally display the Joined table in the Textbos.

#680707 Author: Anil Kumar Pandey Member Level: Platinum Member Rank: 1 Date: 17/Jul/2012 Rating: Points: 2
You can use the Query by joining the table. `Select m.ID, m2.name from Member m, MemberNew m2 where m.ID=m2.ID` Thanks & Regards Anil Kumar Pandey Microsoft MVP, DNS MVM

#680722 Author:

Paritosh Mohapatra Member Level: Diamond Member Rank: 6 Date: 17/Jul/2012 Rating: 2 out of 5

Points: 3

Join the two tables and bind the GridView:


protected void Page_Load(object sender, EventArgs e)
{
        SqlConnection con = new SqlConnection("Your connection string");
        DataSet ds = new DataSet();
        string query = "SELECT t1.*, t2.* FROM Table1 t1, Table2 t2";
        SqlDataAdapter sqlda = new SqlDataAdapter(query, con);
        sqlda.Fill(ds, "Table1_Table2");
        GridView1.DataSource = ds;
        GridView1.DataBind();
    }

Thanks & Regards
Paritosh Mohapatra
Microsoft MVP (ASP.Net/IIS)
DotNetSpider MVM

#680737 Author: Ravindran Member Level: Diamond Member Rank: 3 Date: 17/Jul/2012 Rating: Points: 2
Try like this query `SELECT a.field1, b.field1 FROM Member_ID as a INNER JOIN Member_name ON a.eno=b.eno` This match common record in the tables Regards N.Ravindran Your Hard work never fails

Post Reply

This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post.

	Next : Datagridview validation in vb.net
	Previous : What is Component Based Development?
	Return to Discussion Forum
	Post New Message
	Category:

Related Messages