-
Sep 22nd, 2021, 05:22 PM
#1
Thread Starter
Lively Member
[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
thank you all
-
Sep 23rd, 2021, 01:00 AM
#2
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
-
Sep 23rd, 2021, 12:58 PM
#3
Thread Starter
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|