How do I get specific values from datatable where certain fields match and the sum equals zero?

I have a vb.net datatable that has the following (pertinant) fields:
PK
Amt
AcctNbr
Source

I want to get the items where the AcctNbrs are equal, the Sources are equal and the Amts sum to zero (ie same account numbers & sources where the amounts are $5 and -$5) within the same datatable.

In other words, if I had the data in an actual table, I would do a select like this:
SELECT tbl.PK, tbl.AMT
FROM tbl, tbl AS tbl_1
WHERE tbl.ACCT_NBR=tbl_1.ACCT_NBR AND tbl.AMT+tbl_1.AMT=0 AND tbl.SOURCE=tbl_1.SOURCE

Unfortunately, all the data is already worked over and manipulated and is all within a single datatable.

So, my question is how can I get the data from my datatable?
Thanks in advance!
kevink34Asked:
Who is Participating?
 
tigin44Connect With a Mentor Commented:
You can send the sql statement to the database by using a DBcommand and execute it...
That will give you what you want in a very short time period with compared to the method You did..
0
 
kevink34Author Commented:
I should have added that I could use a filter and loop through each item but I am looking at a faster way because there may be thousands of records and time is important.
I am hoping for a simple way of using the datatable.select method or something like that.
0
 
tigin44Commented:
try this
SELECT tbl.PK, tbl.AMT
FROM tbl T1 INNER JOIN tbl T2 ON T1.ACCT_NBR = T2.ACCT_NBR AND Tl.SOURCE= T2.SOURCE
WHERE (T1.AMT + T2.AMT) = 0
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
kevink34Author Commented:
Thanks for the fast reply.

Where do I put that code?  Again, the data I have to search through is in a dataset.datatable and NOT on an actual table I can query from.
0
 
kevink34Author Commented:
Well, I did end up trying a for next loop:

dvItems.Sort = "PK ASC"
Dim dvTableCopy As DataView = dvItems
     For x As Int32 = 0 To dvItems.Count - 1
          dvTableCopy.RowFilter = "ACCT_NBR='" & dvItems(x).Item("ACCT_NBR") & _
                "' AND AMT=" & dvItems(x).Item("AMT") * -1 & _
                " AND PK>" & dvItems(x).Item("PK")
          If dvTableCopy.Count > 0 Then
               For y As Int32 = 0 To dvTableCopy.Count - 1
                   'These are the items I want
               Next y
    Next

I am still interested in a better, faster way if anyone can come up with one.
Thanks!
Kevin
0
 
kevink34Author Commented:
Thank you.  Again, the issue I am having is that I already have data that has been manipulated and changed in my dataset.  That is the data I want to process.  I know I could go back to the table and get the data but the data that is in the table is different than in the dataset table, if that makes sense....

Thanks again for your help.
0
 
kevink34Author Commented:
It was not exactly what I was looking for.  I mentioned twice that the data was in a datatable already and I did NOT want to go back to the database on the server.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.