Match items from multiple DataTables

[circuits2]

I want to find the instance of each item from one DataTable in another. There will not be duplicates.

Ordinarily I would just use For loops like this:

For Each aRow As DataRow In myDataSet.Tables("Table1").Rows

     For Each bRow As DataRow In myDataSet.Tables("Table2").Rows

          If bRow.Item(0) = aRow.Item(0) Then
               ' Found Matching Items
          End If

     Next bRow

Next aRow

I know that this is supremely innefficient and that there must be a better way. Can someone show me a faster method?

[GaryMazzone]

I would normally do this with a SQL Query using a In clause with a sub query

Select Fields From table1 Where table1PK in Select table1PK from table2

[circuits2]

I don't quite understand your select statement. Can you elaborate?

Say i have two tables:

Table1
2 columns
8 rows

Table 2
2 columns
8 rows

I want to find the item in column 0 from Table1 in Table2 and compare the items in column 1 from each table.

[GaryMazzone]

Select table1.column1 table2.column1 From
table1 inner join table1.column0 = table2.column0

or

Select table1.column1 table2.column1 From
table1,table2 Where table1.column0 In Select table2.column0 From table2

Both get the column1 value from each table where the column0 exsists in both tables.

[circuits2]

I understand querying tables in databases, but I didn't think SQL statements applied to DataTables within DataSets that are in memory. How would you run a SELECT statment against a table in memory?

[circuits2]

Anyone else have an idea?

[GaryMazzone]

Why not query the DB? Something preventing this? Too much time to run the query? If you can't query the datatables in memory then the only choice I know of is to walk the two datatables and check each row against each row.

[circuits2]

I think I may have confused you.

The DataSet that I am working with is not in a DataBase. I have initial information that I pull from the DataBase into the DataSet, but that information is then manipulated and divided into many DataTables.

So lets start over. I have a DataSet in memory (not in a DataBase) that contains several DataTables. I want to compare the data in one DataTable to the data in another DataTable. Each item will only have 1 instance in either table.

What is the best way to do this?

[circuits2]

Perhaps this bmp will help illustrate what I am trying to do....

[daemonkow]

how much manipulation of the data from the db did you do? I am not sure if you can query the dataset, but you can probably write sql statements that will manipulate the data from the db directly in the same fashion you manipulated the data and do the search/compare functions that you need.

[circuits2]

I am way beyond the original information. Let's see if it is easier to attack from this angle:

' Create DataSet
Dim myDataSet As New DataSet

' Add Tables to DataSet
myDataSet.Tables.Add("Table1")
myDataSet.Tables.Add("Table2")

' Add Columns to Tables
myDataSet.Tables("Table1").Columns.Add("Size")
myDataSet.Tables("Table1").Columns.Add("Qty")
myDataSet.Tables("Table2").Columns.Add("Size")
myDataSet.Tables("Table2").Columns.Add("Qty")

'/*
' Populate Tables With Info
' .......Lots of stuff happens here
'*/

' Loop Through Each Item in Table 1
For Each aRow As DataRow In myDataSet.Tables("Table1").Rows
     ' Loop Through Each Item in Table 2 To Find Match
     For Each bRow As DataRow In myDataSet.Tables("Table2").Rows

          If bRow.Item(0) = aRow.Item(0) Then
               ' Found Matching Items
          End If

     Next bRow

Next aRow

[circuits2]

jmcilhinney? kleinma? robdog888? I know there has to be a faster way to do it than this shotgun approach.

[circuits2]

Bump

[circuits2]

Bump