Comparing 2 columns values

Question

Hi,
I have 2 table and 1 column in each one like this;
------------------------------------------------------------------

Column1                Column2
1                           3
2                           4
3                           5
4                           6
...                          ...
------------------------------------------------------------------

I want to compare column1's values with column2, and get the values which arent exist in column2. For example in this instance, I want to get "1" and "2" values.

Obversely, when I compare column2's values with column1, I want to get only these values; "5" and "6"

I tryed datatable, datareader and lots of codes but I didnt get the logic. How can I do that?

Regards.

Satyapriya Nayak · Answer

Hi sleepy, Here is your solution Default2.aspx Untitled Page Text="compare column1's values with column2" onclick="Button1_Click" /> Text="compare column2's values with column1" onclick="Button2_Click" /> Default2.aspx.cs code using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; public partial class Default2 : System.Web.UI.Page { string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; string str; SqlCommand com; protected void Button1_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(strConnString); con.Open(); str = "select * from t1 except select * from t2"; com = new SqlCommand(str, con); SqlDataReader reader = com.ExecuteReader(); GridView1.DataSource = reader; GridView1.DataBind(); con.Close(); } protected void Button2_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(strConnString); con.Open(); str = "select * from t2 except select * from t1"; com = new SqlCommand(str, con); SqlDataReader reader = com.ExecuteReader(); GridView1.DataSource = reader; GridView1.DataBind(); con.Close(); } } Thanks If this post helps you mark it as answer

Vulpes · Answer

It's quite easy if you use the LINQ extension method Except(): using System; using System.Data; using System.Linq; class Test { static void Main() { DataTable dt = new DataTable(); DataColumn dc1 = new DataColumn("Column1",typeof(int)); DataColumn dc2 = new DataColumn("Column2", typeof(int)); dt.Columns.Add(dc1); dt.Columns.Add(dc2); DataRow dr1 = dt.NewRow(); dr1[0] = 1; dr1[1] = 3; DataRow dr2 = dt.NewRow(); dr2[0] = 2; dr2[1] = 4; DataRow dr3 = dt.NewRow(); dr3[0] = 3; dr3[1] = 5; DataRow dr4 = dt.NewRow(); dr4[0] = 4; dr4[1] = 6; dt.Rows.Add(dr1); dt.Rows.Add(dr2); dt.Rows.Add(dr3); dt.Rows.Add(dr4); int[] ia1 = new int[dt.Rows.Count]; int[] ia2 = new int[dt.Rows.Count]; for(int i = 0; i for(int i = 0; i Console.WriteLine("The following are in column1 but not in column2
"); int[] ia3 = ia1.Except(ia2).ToArray(); foreach(int i in ia3) Console.WriteLine(i); Console.WriteLine("
The following are in column2 but not in column1
"); int[] ia4 = ia2.Except(ia1).ToArray(); foreach(int i in ia4) Console.WriteLine(i); Console.ReadKey(); } }