select all the records links with two columns

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

[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

[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