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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

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
tigin44Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.