Member Avatar for pandeysk_13

Hello all,
It’s a problem which is giving me pain.
I have a table agent_registration which have following structure:

agentID    agentname      patentID
001          a              NONE
002          b              001
003          c              001
004          d              002
005          e              003

So, whenever we register a customer whose agentID is ‘005’ then how can we make a query so that all the agent linked with that agentID will be selected and fill in the datagridview.
Like when agentID ‘005’ register a customer then
005 parentID 003 -> 003 is agent whose parentID 001
The datagridview must show

agentID agentname   patentID
001         a       NONE
003         c       001
005         e       003

Please provide me solution for that
Thanks in advance.

Edited by pandeysk_13 because: format
  • 2 Contributors
  • 2 Replies
  • 124 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by pandeysk_13

Dani AI

Generated

Two practical ways to get the full parent chain and show it root-to-leaf (e.g., root, parent, selected agent). As hinted, variable-depth ancestry requires recursion or an application loop; with MySQL 8+ a single recursive query does it, while older MySQL needs an app-side loop.

SQL (MySQL 8+ recursive CTE)

WITH RECURSIVE lineage AS (
  SELECT agentID, agentname, patentID, 1 AS lvl
  FROM agent_registration
  WHERE agentID = '005'         -- start ID (replace with parameter)
  UNION ALL
  SELECT p.agentID, p.agentname, p.patentID, lvl + 1
  FROM agent_registration p
  JOIN lineage l ON p.agentID = l.patentID
  WHERE p.patentID IS NOT NULL AND p.patentID <> 'NONE'
)
SELECT agentID, agentname, patentID
FROM lineage
ORDER BY lvl DESC;              -- root first, selected agent last

Add a parameter instead of the literal ID. The lvl column lets the final ORDER BY produce root-to-leaf output.

VB.NET application loop (works with older MySQL / OleDb)

Dim rows As New List(Of String())
Dim visited As New HashSet(Of String)
Dim currentId As String = startingId

Using conn As New MySqlConnection(connString)
  conn.Open()
  Using cmd As New MySqlCommand("SELECT agentID, agentname, patentID FROM agent_registration WHERE agentID = @id", conn)
    cmd.Parameters.Add("@id", MySqlDbType.VarChar)
    While Not String.IsNullOrEmpty(currentId) AndAlso Not visited.Contains(currentId)
      cmd.Parameters("@id").Value = currentId
      Using rdr = cmd.ExecuteReader()
        If Not rdr.Read() Then Exit While
        rows.Add(New String() {rdr.GetString(0), rdr.GetString(1), If(rdr.IsDBNull(2), "", rdr.GetString(2))})
        visited.Add(currentId)
        currentId = If(rdr.IsDBNull(2), Nothing, rdr.GetString(2))
      End Using
    End While
  End Using
End Using

rows.Reverse()   ' now root->...->selected
' fill a DataTable from rows and set DataGridView.DataSource = that DataTable

Practical notes

  • Guard against cycles by tracking visited IDs (see visited in VB.NET).
  • Prefer NULL for "no parent" instead of strings like "NONE"; makes SQL simpler and faster.
  • Index agentID (and patentID if many lookups) for performance.
  • If using OleDb swap command/parameter types accordingly.
  • For the original reversed output, either ORDER BY the computed level in SQL or reverse the collected list in the app before binding.

This addresses 's ordering issue and gives both a single-query solution (MySQL 8+) and an app-loop fallback (works for older servers).

Recommended Answers

All 2 Replies

Member Avatar for Reverend Jim

If the number of links is variable then you will not be able to do this in a single query. Please see this thread

Member Avatar for pandeysk_13

thanks for response and the link
its doing in the reversing order. means code selecting in downword order.

DataAdapter1 = New OleDbDataAdapter("SELECT agency_ID FROM agency_reg WHERE" _
                                            & "[agency_ID] = '" & parentid.Text & "' OR" _
                                            & "[agency_ID] IN (SELECT DISTINCT agency_ID FROM agency_reg WHERE [agency_ID] LIKE '" & parentid.Text & "')", Conn)

        Dim ds As New DataSet
        ds.Clear()
        DataAdapter1.Fill(ds, "agency_reg")
        DGVteam.DataSource = ds.Tables(0)
        DGVteam.Refresh()

i want it in the upword order like.
agent 005 is under 003, agent 003 is under 001, agent 001 has no parent.
so the datagridview must show in this way:
001
003
005

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.