Results 1 to 9 of 9

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

  1. #1

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    116

    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!
    Last edited by Christhemist; May 4th, 2018 at 10:50 AM.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

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

    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.

  3. #3

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    116

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

    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...

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

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

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    116

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

    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")

  6. #6

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    116

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

    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

  7. #7

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    116

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

    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

  8. #8
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

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

    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.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  9. #9

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    116

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

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width