Buy Questpond's video subscriptions on huge discount.
DotNetFunda.com
Online: 649
Home > Forums > ASP.NET >

Need the value of A-B

Posted by Kondapallisrinivas under ASP.NET on 8/30/2013 | Points: 10 | Views : 2560 | Status : [Member] | Replies : 10
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 
Hi All

I have data in two different data tables.

Datatabel1(Column1,Column2,Column3)
Datatabel2(Col1,col2,col3)

i need to compare the data based on condition(Column1=col1) of both the data tables and show the result of A-B(Nothing but the data which is missing in datatabel1) into another datatabel called as Resultdatatabel.

Regards
Srinivas Kondapalli



Reply | Reply with Attachment
Alert Moderator 

Responses

Posted by: Jayakumars on: 8/30/2013 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi

try this otherwise

explain clearly

Select * FROM T1 tt1 WHERE tt1.colt1 NOT IN(Select colt21 from t2)

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Kondapallisrinivas, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 8/30/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
refer this link
http://forums.asp.net/t/1872550.aspx/1

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Kondapallisrinivas, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Kondapallisrinivas on: 8/30/2013 [Member] Starter | Points: 25

Up
0
Down
Dear Jayakumar

Thanks for replying....I tried the solution but i dint get the expected result....

Issue description:

We have a data in two different data tables

Datatable1 contains the columns----DIST_CD and INV_NO
Datatable2 contains the columns---DistributorCode and Invoiceno

We need to check whether all the distributors in datatable2 are in datatable1 or not....

If some distributors are missing then we need that result into other datatable.

Please let me know if you still not clear about my issue.

Kondapallisrinivas, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 8/30/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down 
html xmlns="http://www.w3.org/1999/xhtml">

<head id="Head1" runat="server">

<title>Gridview onmouseover and onmouseout</title>

<script type="text/javascript">

    var oldgridcolor;

    function SetMouseOver(element) {

        oldgridcolor = element.style.backgroundColor;

        element.style.backgroundColor = '#ffeb95';

        element.style.cursor = 'pointer';

        element.style.textDecoration = 'underline';

    }

    function SetMouseOut(element) {

        element.style.backgroundColor = oldgridcolor;

        element.style.textDecoration = 'none';





    }

</script>

</head>

<body>

<form id="form1" runat="server">

<div>

<asp:GridView runat="server" ID="gvrecords"  AutoGenerateColumns="false" 

HeaderStyle-BackColor="#7779AF" HeaderStyle-ForeColor="White" 

onrowdatabound="gvrecords_RowDataBound">





<Columns>

<asp:BoundField DataField="Name" HeaderText="FirstName" />

<asp:BoundField DataField="ContentType" HeaderText="LastName" />

</Columns>

</asp:GridView> 

</div>

</form>

</body>

</html>

protected void BindGridview()

        {

            string strQuery = "SELECT Name, ContentType FROM  tblFiles WHERE id=1";

            SqlCommand cmd = new SqlCommand(strQuery);

            DataTable dataTab = GetData(cmd);



            string strQuery1 = "SELECT Name, ContentType FROM  tblFiles";

            SqlCommand cmd1 = new SqlCommand(strQuery1);

            DataTable dataTab1 = GetData(cmd1);



            var tab1 = dataTab1.AsEnumerable();

            var tab2 = dataTab.AsEnumerable();



            DataTable dtUncommon = tab2.Except(tab1).CopyToDataTable(); 



            gvrecords.DataSource = dtUncommon;

            gvrecords.DataBind();

        }


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Kondapallisrinivas, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 8/30/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi Srinivas,
Check below script.. and let us know further... 

create table Dis1 (DIST_CD varchar(10) , INV_NO int)

create table Dis2 (DistributorCode varchar(10) , Invoiceno int)



insert into dis2 values('AAA', 1), ('BBB', 2), ('ccc', 3)

insert into dis1 values('AAA', 1), ('BBB', 2), ('DDD',4)



--Method1

SELECT * FROM Dis2

EXCEPT

SELECT * FROM Dis1



-- Method2:

SELECT * FROM Dis2

WHERE DistributorCode NOT IN ( SELECT DIST_CD FROM Dis1)





/*output for above two methods are:

DistributorCode	Invoiceno

ccc		3

*/


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Kondapallisrinivas, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 8/31/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
have you checked the above solution.. if you solved the issue mark it as answer; otherwise post us back

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Kondapallisrinivas, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Kondapallisrinivas on: 9/4/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Bandi,

Thank you very much for your reply....

I tried in the way which you suggested....

DataTable filteredlistHQ=(from hqRow in MYDTHQData.AsEnumerable() join reportRow in MYDTRepData.AsEnumerable() on new {InvDate=hqRow.Field<int>("INV_Date")} equals new {InvDate=reportRow.Field<int>("Date_ID")} where hqRow.Field<string>("INV_NO") == reportRow.Field<string>("invoicenumber") select hqRow).CopyToDataTable();

But here i'm getting the result of equal rows data......But i need not equal rows information....
So can you please suggest some other way to get the not equal row information......

Kondapallisrinivas, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 9/4/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down 
DataTable filteredlistHQ=(from hqRow in MYDTHQData.AsEnumerable() join reportRow in MYDTRepData.AsEnumerable() on new {InvDate=hqRow.Field<int>("INV_Date")} equals new {InvDate=reportRow.Field<int>("Date_ID")} where hqRow.Field<string>("INV_NO") == reportRow.Field<string>("invoicenumber") select hqRow).CopyToDataTable();

The above LINQ is for JOIN with equality condition only.. that is the reason you are getting equal rows....



Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Kondapallisrinivas, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 9/4/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Look into the following script... 
create table Dis1 (DIST_CD varchar(10) , INV_NO int)

create table Dis2 (DistributorCode varchar(10) , Invoiceno int)



insert into dis1 values('AAA', 1), ('BBB', 2), ('ccc', 3)

insert into dis2 values('AAA', 1), ('BBB', 2), ('DDD',4)

--SQL query

SELECT d1.*

FROM Dis1 d1

LEFT JOIN Dis2 d2 ON d1.DIST_CD = d2.DistributorCode

WHERE d2.DistributorCode IS NULL



/*OUTPUT:

DIST_CD	INV_NO

ccc	3

*/



--For the above output equivalent LINQ is as follows

-- LINQ query

var result = (from d1 in Dis1s 

join d2 in Dis2s on d1.DIST_CD equals d2.DistributorCode into temp

from res in temp.DefaultIfEmpty()

where (res.DistributorCode == null)

select new{d1.DIST_CD, d1.INV_NO} );


Here Dis1s = MYDTHQData.AsEnumerable()
Dis2s = MYDTRepData.AsEnumerable()
Tweet the above LINQ for your columns

Reference: http://www.nathanfox.net/blog/74/Check-for-null-in-select-when-using-LINQ-to-Objects-left-outer-join

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Kondapallisrinivas, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Learningtorise on: 9/4/2013 [Member] Starter | Points: 25

Up
0
Down
The solution is very easy... If it is what i understood, following query should solve your problem...

Datatable1 contains the columns----DIST_CD and INV_NO
Datatable2 contains the columns---DistributorCode and Invoiceno

-- This query will return rows from Datatable1 Which are not present in DataTable 2
SELECT * FROM Datatable1 A
LEFT JOIN Datatable2 B ON A.DIST_CD = B.DistributorCode AND A.INV_NO = B.Invoiceno
WHERE B.DistributorCode IS NULL

-- Query to get result in a table(just add into TableName in above Query)
SELECT * INTO #ResultTable
FROM Datatable1 A
LEFT JOIN Datatable2 B ON A.DIST_CD = B.DistributorCode AND A.INV_NO = B.Invoiceno
WHERE B.DistributorCode IS NULL

SELECT * FROM #ResultTable


http://hashtagakash.wordpress.com/

Kondapallisrinivas, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response

Latest Posts