Datatable based on two other datatables (inner join)

[Gene Ariani]

I have one dataset that contains two DataTables:



DataTable1:

Customer_ID

1

2

3

4

5



the other DataTable2 returns

Customer_ID

4

5



If I would have treated this as regular queries and join them on Customer_ID
the resulting set would only contain:



Customer_ID

4

5



Is there a way to inner join the two DataTables to create a third DataTable
with the desired results. I have tried to user Relations.Add method with no
success.



Thanks



Gene

 

[Prateek]

To work with a DataRelation and get joined rows, you will need to use the
GetChildRows method of DataRow and pass the DataRelation object. Below is a
code extract. There is no direct way to get the child rows.

------------------------------------
The following code example creates a DataRelation between the Customers
table and the Orders table of a DataSet and returns all the orders for each
customer.

Dim custOrderRel As DataRelation = custDS.Relations.Add("CustOrders", _
custDS.Tables("Customers").Columns("CustomerID"), _
custDS.Tables("Orders").Columns("CustomerID"))

Dim custRow As DataRow
Dim orderRow As DataRow

For Each custRow in custDS.Tables("Customers").Rows
Console.WriteLine(custRow("CustomerID"))
For Each orderRow in custRow.GetChildRows(custOrderRel)
Console.WriteLine(orderRow("OrderID"))
Next
Next
------------------------------------

You can however, create a separate command object, associate the join SQL
query with it and populate a third DataTable in the DataSet using this
command object.

-Prateek

I have one dataset that contains two DataTables:



DataTable1:

Customer_ID

1

2

3

4

5



the other DataTable2 returns

Customer_ID

4

5



If I would have treated this as regular queries and join them on Customer_ID
the resulting set would only contain:



Customer_ID

4

5



Is there a way to inner join the two DataTables to create a third DataTable
with the desired results. I have tried to user Relations.Add method with no
success.



Thanks



Gene