Need the value of A-B

Posted by Kondapallisrinivas under ASP.NET on 8/30/2013 | Points: 10 | Views : 1530 | Status : [Member] | Replies : 10
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




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

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