How do I create a SQL join query using multiple DataTables?

I'm trying to create a more simplistic view builder in Visual Studio/VB.net. The number of DataTables being used will vary, but we'll use 3 as an example. I have 1 DataTable that is Addresses with a "Code" field, and 2 Matrix DataTables, each with a corresponding "Code" field that will be used to map to the address list. For example:

Address Input:
First
Last
Address
City
State
Zip
Code

Matrix 1 Input
Code
Offer1
Date1

Matrix 2 Input
Code
Offer2
Date2

Combined Output:
First
Last
Address
City
State
Zip
Code
Offer1
Date1
Offer2
Date2

This is easily done in SQL using an Inner Join Query, which I would like to replicate in VB.net. I am having trouble with syntax and connection strings to the multiple DataTables involved. Here is what I have so far: (Keep in mind that all 3 datatables in the example have been populated already with data)

Code:

---

Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBase IV;Data Source='Address'")

        Dim sql As String = "SELECT Address.FIRST, Address.LAST, Address.ADDRESS, Address.CITY, Address.STATE, Address.ZIP, Address.CODE, Matrix1.OFFER1, Matrix1.DATE1, Matrix2.OFFER1, Matrix2.DATE1
                                                        FROM Matrix2 INNER JOIN (Matrix1 INNER JOIN Address ON Matrix1.CODE = Address.CODE) ON Matrix2.CODE = Address.CODE;"
        cn.Open()
       
        Dim da As OleDbDataAdapter = New OleDbDataAdapter
               
        Dim CombinedDT As New DataTable
               
        Using da As New OleDbDataAdapter(sql, cn)

            da.FillSchema(CombinedDT, SchemaType.Source)
            da.Fill(CombinedDT)

        End Using

---

Note: I am trying to put the combined data into a new DataTable

Any help would be greatly appreciated!!

Thank you!

Firstly, calling FillSchema before calling Fill is pointless because Fill does what FillSchema does anyway. You only call FillSchema if you want schema but no data, while Fill will give you schema and data.

As for the question, you can't query DataTables like that using ADO.NET. You can create a DataTableReader to access the data in one or more DataTables via a data reader as though you were querying a database but that just provides access to each and every record in each and every DataTable in serial. There's no provision to sort, filter or project though. You have a couple of options:

1. Add the DataTables to a DataSet and create appropriate DataRelations between them. Loop through the Rows of the addresses table and, for each DataRow, call its GetChildRows method for each DataRelation to get the related data from the other two tables.
2. Write a LINQ to DataSet query including Join statements.

In either case, you'd have to create your own DataTable to store the combined data first. You can then create a DataRow for that DataTable for each result using either option above.

Thanks for your reply, jmcilhinney! I took your advice (partially) and this is what I came up with:

Code:

---

Dim both = From row1 In AddressListDatatable.AsEnumerable()
                  Join row2 In Matrix1Datatable.AsEnumerable()
                  On row1.Field(Of String)("Code") Equals row2.Field(Of String)("Code")

        For Each r1r2 In both

            Dim row1 = String.Format("{0}", String.Join(",", r1r2.row1.ItemArray))
            Dim row2 = String.Format("{0}", String.Join(",", r1r2.row2.ItemArray))

            Next

---

This block of code returns the correct data, but it returns all of the fields in each table. How do I specify which fields to return like I would in a SQL statement? I'm trying to avoid looping through the results, because doing so with the amount of records this will be used for will be slower than I want.

Also, how would I go about joining together multiple tables? My example above only involves two (AddressList & Matrix1) I will have some instances where I will need to join upwards of 4 tables...

Add a select clause...
Shooting from the hip here:

Code:

---


Dim both = From row1 In AddressListDatatable.AsEnumerable()
                  Join row2 In Matrix1Datatable.AsEnumerable()
                  On row1.Field(Of String)("Code") Equals row2.Field(Of String)("Code")
                  Select row1.field1, row1.field2, row2.nudderfield, row2.onemorefield

---

as for joining additional tables... same as the others... with a join clause.

-tg

It's not letting me select more than one field, am I missing something in the syntax below?

Code:

---

Dim both = From row1 In AddressListDatatable.AsEnumerable()
                  Join row2 In Matrix1Datatable.AsEnumerable()
                  On row1.Field(Of String)("Code") Equals row2.Field(Of String)("Code")
                  Select row1.Field(Of String)("FirstName")

---

Ok i got it to select multiple columns, but now I am having trouble getting just the values rather than the Field name and Value: For example, I using this code:

Code:

---

Select Account = row1.Item("Account"), First = row1.Item("First")

---

And it is returning {Account = 0000000, First = Chris}

I just need the values like this: 000000, Chris

This thread had been solved. I realize that I now have a completely different issue regarding fields values and have moved that question to this thread: http://www.vbforums.com/showthread.p...only-with-LINQ

I know it's not your question but my inner DBA insists on pointing out that there's probably either something wrong with your design or with your original sql query.

Can there be multiple Matrix 1 records for a given code or is code unique in the Matrix 1 table?
Same question for Matrix 2.

If multiples are allowed then your query will "multiply" the output. So a code that has 3 "Matrix 1" entries and 3 "Matrix 2" entries will produce 9 records in the output, not the 3 records you seem to be expecting.

If multiples are not allowed then this looks like a 1 to 1 relationship, in which case the matrix tables shouldn't exist and their data should be help in the Address table.

Or is this actually a 1 to zero relationship between Address and the two matrix tables, in which case you almost certainly want Left or Right Outer joins in your query instead of the Inner Joins you've used.

Thanks for the insight, FunkyDexter!

However, there are not multiple records per code in each matrix, it is unique to the individual record.

The data comes over to me separate from the matrix and I have to combine the multiple lists in Microsoft Access currently, but I hate the Access Query interface so I am building my own in a vb.net app.