Results 1 to 3 of 3

Thread: [RESOLVED] Multiple inner joins to Populate a treeview

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Mar 2021
    Posts
    108

    Resolved [RESOLVED] Multiple inner joins to Populate a treeview

    Hello everyone
    Please VbForums help me
    I'm using multiple inner joins to populate a treeview from access db
    the attached image illustrates my need.
    This is my query but I'm not getting what I expect.

    Code:
    Dim ParentName As String
        Dim FieldIndex As Integer
        Dim NodeName As String
        Dim NewNode As Node
        
        Set cnn = New ADODB.Connection
        With cnn
    '        .Provider = "Microsoft.Jet.OLEDB.4.0" 
            .ConnectionString = "User ID=Admin;password=;" & " Data Source=" & App.Path & "\data.accdb;"
            .CursorLocation = adUseClient
            .Open
        End With
        
        Set cmd = New ADODB.Command
        Set cmd.ActiveConnection = cnn
        
        cmd.CommandText = "select ID, item1, item2, item3, item4 from T1 " & _
    " inner join T2 on  T1.Id = T2.CID " & _
    " inner join T3 on  T2.FId = T3.PID " & _
    " inner join T4 on  T3.SID = T4.LID
        Set rs = cmd.Execute
        
                    Do While Not rs.EOF
                    ParentName = vbNullString
                    For FieldIndex = 1 To rs.Fields.Count - 1
                        NodeName = ParentName & "|" & rs.Fields(FieldIndex).Value
                        On Error Resume Next
                        Set NewNode = TreeView1.Nodes.Item(NodeName)
                        If Err Then
                            On Error GoTo 0
                            If Len(ParentName) = 0 Then
                                Set NewNode = TreeView1.Nodes.Add(, _
                                                                  tvwNext, _
                                                                  NodeName, _
                                                                  rs.Fields(FieldIndex).Value)
                            Else
                                Set NewNode = TreeView1.Nodes.Add(ParentName, _
                                                                  tvwChild, _
                                                                  NodeName, _
                                                                 rs.Fields(FieldIndex).Value)
                            End If
    '                        NewNode.Expanded = True
                            NewNode.Sorted = True
                        Else
                            On Error GoTo 0
                        End If
                        ParentName = NodeName
                    Next
                    rs.MoveNext
                Loop
                rs.Close
    These are my tables and this is what I hope to get as outcome

    Name:  pic.jpg
Views: 106
Size:  24.4 KB

    thank you all

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Multiple inner joins to Populate a treeview

    Don't do inner joins in this case
    Basically, you'd have 4 (nested) nested loops
    Grab the data from T1
    If T1.RecordCount>0 Start First loop
    Create Parent Node and enter data from T1
    Grab Data from T2 with T2.FK=T1.PK
    If T2.RecordCount>0 Start second Loop
    Create Child Node and enter Data from T2
    Grab Data from T3 with T3.FK=T2.PK
    If T3.RecordCount>0 Start third Loop
    Create Child Node and enter Data from T3
    Grab Data from T4 with T4.FK=T3.PK
    If T4.RecordCount>0 Start fourth Loop
    Create Child Node and enter Data from T4
    End fourth loop
    End third loop
    End second loop
    End first loop

    EDIT: The Alternative is a LEFT JOIN instead of INNER JOIN if you want to do it in one loop.
    Then you can check with a IsNull if you need to create a child-node

    EDIT2: The first version can be done recursively passing the then parent node and optional identifiers for the next query to use as table and Keynames
    Last edited by Zvoni; Sep 23rd, 2021 at 05:38 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Mar 2021
    Posts
    108

    Re: Multiple inner joins to Populate a treeview

    thank you very much
    solved
    Last edited by Adebiyi24; Sep 23rd, 2021 at 07:22 PM.

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