ASP Looping, Recursion to display hierarchy (How to?)

Discussion in 'ASP General' started by Matt, Dec 4, 2008.

  1. Matt

    Matt Guest

    I am having some serious brain-melting session just trying to wrap my head
    around what I'm trying to accomplish, but unfortunately I've never been able
    to properly put it into words. I found some articles on using recursion to
    display a hierarchy on the file system. Unfortunately, I think I GET the
    basic concept of what recursion means now (I think, but I'm likely wrong),
    but I cannot figure out how to migrate the practice into how my database
    works.

    I have two tables. One table has "Locations" (tb_locations), and another
    table has "Machines" (tb_machines). Being this as the case, I have a SQL JOIN
    query to allow me to pull all the information from both tables.

    My SQL Query is as follows:
    Code:
    "SELECT * FROM tb_locations p LEFT JOIN tb_machines c ON
    p.f_locationID = c.f_machineparent ORDER BY p.f_locationname,
    c.f_machinename"
    As you can see, tb_machines.f_machineparent references
    tb_locations.f_locationID. I have looked through literally thousands of pages
    of Google results and have found hundreds of examples of recursion, but none
    dealing with my scenario of two related database tables.

    Here is my current code, which does get the first "Location" and the first
    "Machine" related to that location, but it simply loops through that machine
    hundreds of times until I get "Out of Memory" errors:
    Code:
    		Call ListCategory(0)
    Sub ListCategory(parentID)
    ' -- Create Recordset --
    Dim rs_clients
    Dim rs_clients_cmd
    Dim rs_clients_numRows
    
    Set rs_clients_cmd = Server.CreateObject ("ADODB.Command")
    rs_clients_cmd.ActiveConnection = MM_conn_beaconreader_STRING
    rs_clients_cmd.CommandText = "SELECT * FROM tb_locations p LEFT JOIN
    tb_machines c ON p.f_locationID = c.f_machineparent ORDER BY
    p.f_locationname, c.f_machinename"
    rs_clients_cmd.Prepared = true
    
    Set rs_clients = rs_clients_cmd.Execute
    rs_clients_numRows = 0
    
    ' -- Loop Through Pages --
    If Not rs_clients.EOF Then
    Do While Not rs_clients.EOF
    response.write Space(10) & "<div id=""sitemap-category"">" & vbCrLf
    response.write Space(12) & "<ul>" & vbCrLf
    response.write Space(14) & "<li class=""parent"">" &
    rs_clients("f_locationname") & "</a></li>" & vbCrLf
    
    Call ListSubCategory(rs_clients("f_locationID"), 1)
    
    response.write Space(14) & "<div id=""clear""></div>" & vbCrLf
    response.write Space(12) & "</ul>" & vbCrLf
    response.write Space(10) & "</div>" & vbCrLf
    
    intCounter = intCounter + 1
    If intCounter Mod 3 = 0 Then
    response.write Space(10) & "<div id=""clear""></div>" & vbCrLf
    End If
    rs_clients.MoveNext
    Loop
    End If
    
    ' -- Clear Divs --
    If intCounter Mod 3 <> 0 Then
    response.write Space(10) & "<div id=""clear""></div>"
    End If
    
    ' -- Close Connection --
    rs_clients.Close
    Set rs_clients = Nothing
    End Sub
    
    
    Sub ListSubCategory(f_locationID, Counter)
    Dim rs_machines
    Dim rs_machines_cmd
    Dim rs_machines_numRows
    
    Set rs_machines_cmd = Server.CreateObject ("ADODB.Command")
    rs_machines_cmd.ActiveConnection = MM_conn_beaconreader_STRING
    rs_machines_cmd.CommandText = "SELECT * FROM tb_locations p LEFT JOIN
    tb_machines c ON p.f_locationID = c.f_machineparent ORDER BY
    p.f_locationname, c.f_machinename"
    rs_machines_cmd.Prepared = true
    
    Set rs_machines = rs_machines_cmd.Execute
    rs_machines_numRows = 0
    
    ' -- Loop Through Pages --
    If Not rs_machines.EOF Then
    Do While Not rs_machines.EOF
    response.write Space(14) & "<li style=""margin-left: " & (Counter *
    10) & "px;"">" & rs_machines("f_machinename") & "</li>" & vbCrLf
    
    
    Counter = Counter + 1
    Call ListSubCategory(rs_machines("f_locationID"), Counter)
    Counter = Counter - 1
    rs_machines.MoveNext
    Loop
    End If
    
    ' -- Close Connection --
    rs_machines.Close
    Set rs_machines = Nothing
    End Sub
    
    Result:
    Code:
    - Paris
    - machine01
    -machine01
    -machine01
    -machine01
    -machine01
    ....etc
    
    So at some point I'm missing a step on getting out of that first machine's
    loop and moving onto the second machine. Does anybody have any experience
    with this?

    Thanks in advance,
    Matt
    Matt, Dec 4, 2008
    #1
    1. Advertising

  2. "Matt" wrote:
    >
    > Sub ListSubCategory(f_locationID, Counter)
    > ...
    > Call ListSubCategory(rs_machines("f_locationID"), Counter)


    You are passing a value for f_locationID, but never using it.



    --
    Dave Anderson

    Unsolicited commercial email will be read at a cost of $500 per message. Use
    of this email address implies consent to these terms.
    Dave Anderson, Dec 4, 2008
    #2
    1. Advertising

  3. Matt

    Evertjan. Guest

    =?Utf-8?B?TWF0dA==?= wrote on 04 dec 2008 in
    microsoft.public.inetserver.asp.general:

    > If intCounter Mod 3 = 0 Then
    > response.write Space(10) & "<div id=""clear""></div>" & vbCrLf
    > End If
    > rs_clients.MoveNext
    > Loop
    >


    Having multiple id's with the same value is illegal.

    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
    Evertjan., Dec 4, 2008
    #3
  4. "Matt" <> wrote in message
    news:...
    >I am having some serious brain-melting session just trying to wrap my head
    > around what I'm trying to accomplish, but unfortunately I've never been
    > able
    > to properly put it into words. I found some articles on using recursion to
    > display a hierarchy on the file system. Unfortunately, I think I GET the
    > basic concept of what recursion means now (I think, but I'm likely wrong),
    > but I cannot figure out how to migrate the practice into how my database
    > works.
    >
    > I have two tables. One table has "Locations" (tb_locations), and another
    > table has "Machines" (tb_machines). Being this as the case, I have a SQL
    > JOIN
    > query to allow me to pull all the information from both tables.
    >
    > My SQL Query is as follows:
    >
    Code:
    "SELECT * FROM tb_locations p LEFT JOIN tb_machines c ON
    > p.f_locationID = c.f_machineparent ORDER BY p.f_locationname,
    > c.f_machinename"
    >


    I don't see a recursive relationship here. It doesn't help that * in the
    select really fails to inform and is not good practice, list all the fields
    needed. My guess is that if there is a recursive relationship its because
    Locations point to sub locations. Is this the case? If the location table
    does not refer to itself or the machine table does not have more than one
    relationship to the location table then you don't have a recursive system.

    Your post has the problem of have a good many things wrong with it. Most
    unrelated to your question. We need to take in steps. First we need to
    establish is there really a recursive relationship here or not?

    --
    Anthony Jones - MVP ASP/ASP.NET
    Anthony Jones, Dec 4, 2008
    #4
  5. Matt

    Mike Brind Guest

    "Matt" <> wrote in message
    news:...
    >I am having some serious brain-melting session just trying to wrap my head
    > around what I'm trying to accomplish, but unfortunately I've never been
    > able
    > to properly put it into words. I found some articles on using recursion to
    > display a hierarchy on the file system. Unfortunately, I think I GET the
    > basic concept of what recursion means now (I think, but I'm likely wrong),
    > but I cannot figure out how to migrate the practice into how my database
    > works.
    >
    > I have two tables. One table has "Locations" (tb_locations), and another
    > table has "Machines" (tb_machines). Being this as the case, I have a SQL
    > JOIN
    > query to allow me to pull all the information from both tables.
    >
    > My SQL Query is as follows:
    >
    Code:
    "SELECT * FROM tb_locations p LEFT JOIN tb_machines c ON
    > p.f_locationID = c.f_machineparent ORDER BY p.f_locationname,
    > c.f_machinename"
    >
    > As you can see, tb_machines.f_machineparent references
    > tb_locations.f_locationID. I have looked through literally thousands of
    > pages
    > of Google results and have found hundreds of examples of recursion, but
    > none
    > dealing with my scenario of two related database tables.
    >
    > Here is my current code, which does get the first "Location" and the first
    > "Machine" related to that location, but it simply loops through that
    > machine
    > hundreds of times until I get "Out of Memory" errors:
    >
    Code:
     Call ListCategory(0)
    > Sub ListCategory(parentID)
    > ' -- Create Recordset --
    > Dim rs_clients
    > Dim rs_clients_cmd
    > Dim rs_clients_numRows
    >
    > Set rs_clients_cmd = Server.CreateObject ("ADODB.Command")
    > rs_clients_cmd.ActiveConnection = MM_conn_beaconreader_STRING
    > rs_clients_cmd.CommandText = "SELECT * FROM tb_locations p LEFT JOIN
    > tb_machines c ON p.f_locationID = c.f_machineparent ORDER BY
    > p.f_locationname, c.f_machinename"
    > rs_clients_cmd.Prepared = true
    >
    > Set rs_clients = rs_clients_cmd.Execute
    > rs_clients_numRows = 0
    >
    > ' -- Loop Through Pages --
    > If Not rs_clients.EOF Then
    > Do While Not rs_clients.EOF
    > response.write Space(10) & "<div id=""sitemap-category"">" & vbCrLf
    > response.write Space(12) & "<ul>" & vbCrLf
    > response.write Space(14) & "<li class=""parent"">" &
    > rs_clients("f_locationname") & "</a></li>" & vbCrLf
    >
    > Call ListSubCategory(rs_clients("f_locationID"), 1)
    >
    > response.write Space(14) & "<div id=""clear""></div>" & vbCrLf
    > response.write Space(12) & "</ul>" & vbCrLf
    > response.write Space(10) & "</div>" & vbCrLf
    >
    > intCounter = intCounter + 1
    > If intCounter Mod 3 = 0 Then
    > response.write Space(10) & "<div id=""clear""></div>" & vbCrLf
    > End If
    > rs_clients.MoveNext
    > Loop
    > End If
    >
    > ' -- Clear Divs --
    > If intCounter Mod 3 <> 0 Then
    > response.write Space(10) & "<div id=""clear""></div>"
    > End If
    >
    > ' -- Close Connection --
    > rs_clients.Close
    > Set rs_clients = Nothing
    > End Sub
    >
    >
    > Sub ListSubCategory(f_locationID, Counter)
    > Dim rs_machines
    > Dim rs_machines_cmd
    > Dim rs_machines_numRows
    >
    > Set rs_machines_cmd = Server.CreateObject ("ADODB.Command")
    > rs_machines_cmd.ActiveConnection = MM_conn_beaconreader_STRING
    > rs_machines_cmd.CommandText = "SELECT * FROM tb_locations p LEFT JOIN
    > tb_machines c ON p.f_locationID = c.f_machineparent ORDER BY
    > p.f_locationname, c.f_machinename"
    > rs_machines_cmd.Prepared = true
    >
    > Set rs_machines = rs_machines_cmd.Execute
    > rs_machines_numRows = 0
    >
    > ' -- Loop Through Pages --
    > If Not rs_machines.EOF Then
    > Do While Not rs_machines.EOF
    > response.write Space(14) & "<li style=""margin-left: " & (Counter *
    > 10) & "px;"">" & rs_machines("f_machinename") & "</li>" & vbCrLf
    >
    >
    > Counter = Counter + 1
    > Call ListSubCategory(rs_machines("f_locationID"), Counter)
    > Counter = Counter - 1
    > rs_machines.MoveNext
    > Loop
    > End If
    >
    > ' -- Close Connection --
    > rs_machines.Close
    > Set rs_machines = Nothing
    > End Sub
    > 
    > Result:
    Code:
    > - Paris
    >       - machine01
    >              -machine01
    >                     -machine01
    >                            -machine01
    >                                   -machine01
    > ...etc
    > 
    >
    > So at some point I'm missing a step on getting out of that first machine's
    > loop and moving onto the second machine. Does anybody have any experience
    > with this?
    >
    > Thanks in advance,
    > Matt


    Like the others, I don't see a recursive relationship here either. Getting
    the contents of folders generally requires recursion, because there is no
    way of knowing how many levels of nested subfolders there are, so you
    construct a method or function to look at a folder, then list its contents.
    That function then calls itself on all folders found within the folder
    contents, lists the contents, and then calls itself on all folders found
    within the subfolder contents, lists the contents, and then calls itself
    on....

    You hopefully get the point.

    What you appear to be doing in your case is to display a Master-Child or
    One-To-Many relationship. The inefficient way to do this is to select all
    the locations, and the for each location, fire a select command to obtain
    the related machines. That is the way you see code samples show this most
    often. But there are better ways, not least this one:

    http://databases.aspfaq.com/database/how-do-i-present-one-to-many-relationships-in-my-asp-page.html

    --
    Mike Brind
    MVP - ASP/ASP.NET
    Mike Brind, Dec 5, 2008
    #5
  6. Matt

    Matt Guest

    "Mike Brind" wrote:

    >
    > "Matt" <> wrote in message
    > news:...
    > >I am having some serious brain-melting session just trying to wrap my head
    > > around what I'm trying to accomplish, but unfortunately I've never been
    > > able
    > > to properly put it into words. I found some articles on using recursion to
    > > display a hierarchy on the file system. Unfortunately, I think I GET the
    > > basic concept of what recursion means now (I think, but I'm likely wrong),
    > > but I cannot figure out how to migrate the practice into how my database
    > > works.
    > >
    > > I have two tables. One table has "Locations" (tb_locations), and another
    > > table has "Machines" (tb_machines). Being this as the case, I have a SQL
    > > JOIN
    > > query to allow me to pull all the information from both tables.
    > >
    > > My SQL Query is as follows:
    > >
    Code:
    "SELECT * FROM tb_locations p LEFT JOIN tb_machines c ON
    > > p.f_locationID = c.f_machineparent ORDER BY p.f_locationname,
    > > c.f_machinename"
    > >
    > > As you can see, tb_machines.f_machineparent references
    > > tb_locations.f_locationID. I have looked through literally thousands of
    > > pages
    > > of Google results and have found hundreds of examples of recursion, but
    > > none
    > > dealing with my scenario of two related database tables.
    > >
    > > Here is my current code, which does get the first "Location" and the first
    > > "Machine" related to that location, but it simply loops through that
    > > machine
    > > hundreds of times until I get "Out of Memory" errors:
    > >
    Code:
     Call ListCategory(0)
    > > Sub ListCategory(parentID)
    > > ' -- Create Recordset --
    > > Dim rs_clients
    > > Dim rs_clients_cmd
    > > Dim rs_clients_numRows
    > >
    > > Set rs_clients_cmd = Server.CreateObject ("ADODB.Command")
    > > rs_clients_cmd.ActiveConnection = MM_conn_beaconreader_STRING
    > > rs_clients_cmd.CommandText = "SELECT * FROM tb_locations p LEFT JOIN
    > > tb_machines c ON p.f_locationID = c.f_machineparent ORDER BY
    > > p.f_locationname, c.f_machinename"
    > > rs_clients_cmd.Prepared = true
    > >
    > > Set rs_clients = rs_clients_cmd.Execute
    > > rs_clients_numRows = 0
    > >
    > > ' -- Loop Through Pages --
    > > If Not rs_clients.EOF Then
    > > Do While Not rs_clients.EOF
    > > response.write Space(10) & "<div id=""sitemap-category"">" & vbCrLf
    > > response.write Space(12) & "<ul>" & vbCrLf
    > > response.write Space(14) & "<li class=""parent"">" &
    > > rs_clients("f_locationname") & "</a></li>" & vbCrLf
    > >
    > > Call ListSubCategory(rs_clients("f_locationID"), 1)
    > >
    > > response.write Space(14) & "<div id=""clear""></div>" & vbCrLf
    > > response.write Space(12) & "</ul>" & vbCrLf
    > > response.write Space(10) & "</div>" & vbCrLf
    > >
    > > intCounter = intCounter + 1
    > > If intCounter Mod 3 = 0 Then
    > > response.write Space(10) & "<div id=""clear""></div>" & vbCrLf
    > > End If
    > > rs_clients.MoveNext
    > > Loop
    > > End If
    > >
    > > ' -- Clear Divs --
    > > If intCounter Mod 3 <> 0 Then
    > > response.write Space(10) & "<div id=""clear""></div>"
    > > End If
    > >
    > > ' -- Close Connection --
    > > rs_clients.Close
    > > Set rs_clients = Nothing
    > > End Sub
    > >
    > >
    > > Sub ListSubCategory(f_locationID, Counter)
    > > Dim rs_machines
    > > Dim rs_machines_cmd
    > > Dim rs_machines_numRows
    > >
    > > Set rs_machines_cmd = Server.CreateObject ("ADODB.Command")
    > > rs_machines_cmd.ActiveConnection = MM_conn_beaconreader_STRING
    > > rs_machines_cmd.CommandText = "SELECT * FROM tb_locations p LEFT JOIN
    > > tb_machines c ON p.f_locationID = c.f_machineparent ORDER BY
    > > p.f_locationname, c.f_machinename"
    > > rs_machines_cmd.Prepared = true
    > >
    > > Set rs_machines = rs_machines_cmd.Execute
    > > rs_machines_numRows = 0
    > >
    > > ' -- Loop Through Pages --
    > > If Not rs_machines.EOF Then
    > > Do While Not rs_machines.EOF
    > > response.write Space(14) & "<li style=""margin-left: " & (Counter *
    > > 10) & "px;"">" & rs_machines("f_machinename") & "</li>" & vbCrLf
    > >
    > >
    > > Counter = Counter + 1
    > > Call ListSubCategory(rs_machines("f_locationID"), Counter)
    > > Counter = Counter - 1
    > > rs_machines.MoveNext
    > > Loop
    > > End If
    > >
    > > ' -- Close Connection --
    > > rs_machines.Close
    > > Set rs_machines = Nothing
    > > End Sub
    > > 
    > > Result:
    Code:
    > > - Paris
    > >       - machine01
    > >              -machine01
    > >                     -machine01
    > >                            -machine01
    > >                                   -machine01
    > > ...etc
    > > 
    > >
    > > So at some point I'm missing a step on getting out of that first machine's
    > > loop and moving onto the second machine. Does anybody have any experience
    > > with this?
    > >
    > > Thanks in advance,
    > > Matt

    >
    > Like the others, I don't see a recursive relationship here either. Getting
    > the contents of folders generally requires recursion, because there is no
    > way of knowing how many levels of nested subfolders there are, so you
    > construct a method or function to look at a folder, then list its contents.
    > That function then calls itself on all folders found within the folder
    > contents, lists the contents, and then calls itself on all folders found
    > within the subfolder contents, lists the contents, and then calls itself
    > on....
    >
    > You hopefully get the point.
    >
    > What you appear to be doing in your case is to display a Master-Child or
    > One-To-Many relationship. The inefficient way to do this is to select all
    > the locations, and the for each location, fire a select command to obtain
    > the related machines. That is the way you see code samples show this most
    > often. But there are better ways, not least this one:
    >
    > http://databases.aspfaq.com/database/how-do-i-present-one-to-many-relationships-in-my-asp-page.html
    >
    > --
    > Mike Brind
    > MVP - ASP/ASP.NET
    >
    >
    >


    Mike, thanks for the reply. That was one of the first articles that I
    referenced, and it works....to a point. First, maybe I'm not doing recursion,
    I guess that's why I posted here because I'm really unsure about how to
    "term" what I am doing, which makes Internet searches for help very difficult.

    Second, back to the meat of our discussion. The link you showed is what I
    used previously to test what I have, but that limits me to a single
    parent-child relationship, where I have a parent, with children, who have
    children, who have children, continuing infinitely (though I hope not). Since
    apparently this is not recursion, that would explain why I cannot find any
    examples online.

    Here is a basic structure of what I intend to accomplish:

    San Francisco
    |
    |---> Car Dealership 1
    |
    |--->Machine01
    |--->Machine02
    |
    |---> Car Dealership 2
    |
    |--->West Wing
    |--->Machine03
    |--->Machine04
    |--->East Wing
    |--->Machine05
    |--->Machine06
    |--->Machine07

    ------------------------------------
    So locations can be children of other locations. If location equals zero
    (0), then it is a top-level location, otherwise it's f_locationparent (sorry,
    I was definitely not clear enough in my first post) can reference the
    f_locationID of another location:

    Here is some sample data (used in the little example hierarchy above):
    CREATE TABLE [dbo].[tb_locations](
    [f_locationID] [int] IDENTITY(1,1) NOT NULL,
    [f_locationcompany] [int] NOT NULL,
    [f_locationparent] [int] NOT NULL,
    [f_locationname] [nvarchar](50) NULL,
    [f_locationlogofile] [nvarchar](50) NULL

    CREATE TABLE [dbo].[tb_machines](
    [f_machineID] [int] IDENTITY(1,1) NOT NULL,
    [f_machineGUID] [nvarchar](50) NOT NULL,
    [f_machineparent] [int] NOT NULL,
    [f_machineserial] [nvarchar](50) NULL,
    [f_machinename] [nvarchar](50) NULL,
    [f_OSversion] [nchar](10) NULL,
    [f_lastreported] [datetime] NULL,
    [f_lastIPaddresses] [nvarchar](50) NULL
    =====================================
    Some data within the tables (used as the hierarchy display above) would be
    as follows:

    INSERT INTO tb_locations(f_locationID, f_locationparent,f_locationname)
    VALUES (1,0,San Francisco)
    INSERT INTO tb_locations(f_locationID, f_locationparent,f_locationname)
    VALUES (2,1,Car Dealership 1)
    INSERT INTO tb_locations(f_locationID, f_locationparent,f_locationname)
    VALUES (3,1,Car Dealership 2)
    INSERT INTO tb_locations(f_locationID, f_locationparent,f_locationname)
    VALUES (4,3,West Wing)
    INSERT INTO tb_locations(f_locationID, f_locationparent,f_locationname)
    VALUES (5,3,West Wing)

    INSERT INTO tb_machines(f_machineparent,f_machinename) VALUES (2,Machine01)
    INSERT INTO tb_machines(f_machineparent,f_machinename) VALUES (2,Machine02)
    INSERT INTO tb_machines(f_machineparent,f_machinename) VALUES (4,Machine03)
    INSERT INTO tb_machines(f_machineparent,f_machinename) VALUES (4,Machine04)
    INSERT INTO tb_machines(f_machineparent,f_machinename) VALUES (5,Machine05)
    INSERT INTO tb_machines(f_machineparent,f_machinename) VALUES (5,Machine06)
    INSERT INTO tb_machines(f_machineparent,f_machinename) VALUES (3,Machine07)

    Thanks for the reply Mike, I appreciate it.
    Matt
    Matt, Dec 5, 2008
    #6
  7. Matt

    Matt Guest

    "Anthony Jones" wrote:

    > "Matt" <> wrote in message
    > news:...
    > >I am having some serious brain-melting session just trying to wrap my head
    > > around what I'm trying to accomplish, but unfortunately I've never been
    > > able
    > > to properly put it into words. I found some articles on using recursion to
    > > display a hierarchy on the file system. Unfortunately, I think I GET the
    > > basic concept of what recursion means now (I think, but I'm likely wrong),
    > > but I cannot figure out how to migrate the practice into how my database
    > > works.
    > >
    > > I have two tables. One table has "Locations" (tb_locations), and another
    > > table has "Machines" (tb_machines). Being this as the case, I have a SQL
    > > JOIN
    > > query to allow me to pull all the information from both tables.
    > >
    > > My SQL Query is as follows:
    > >
    Code:
    "SELECT * FROM tb_locations p LEFT JOIN tb_machines c ON
    > > p.f_locationID = c.f_machineparent ORDER BY p.f_locationname,
    > > c.f_machinename"
    > >

    >
    > I don't see a recursive relationship here. It doesn't help that * in the
    > select really fails to inform and is not good practice, list all the fields
    > needed. My guess is that if there is a recursive relationship its because
    > Locations point to sub locations. Is this the case? If the location table
    > does not refer to itself or the machine table does not have more than one
    > relationship to the location table then you don't have a recursive system.
    >
    > Your post has the problem of have a good many things wrong with it. Most
    > unrelated to your question. We need to take in steps. First we need to
    > establish is there really a recursive relationship here or not?
    >
    > --
    > Anthony Jones - MVP ASP/ASP.NET
    >
    >


    Anthony, thanks for the reply. I posted more information in a reply to Mike
    Brind, so hopefully that better explains the scenario. Also, I always use the
    (*) for my select statements when posting them to support forums / newsgroups
    if it doesn't add to the discussion, otherwise it makes the query look
    overwhelming when simply trying to glance at what it does. Unfortunately, in
    doing that, I always receive the inevitable reply like yours telling me that
    using a wildcard is a bad idea.
    Matt, Dec 5, 2008
    #7
  8. Matt wrote:
    > I am having some serious brain-melting session just trying to wrap my head
    > around what I'm trying to accomplish, but unfortunately I've never been able
    > to properly put it into words. I found some articles on using recursion to
    > display a hierarchy on the file system. Unfortunately, I think I GET the
    > basic concept of what recursion means now (I think, but I'm likely wrong),
    > but I cannot figure out how to migrate the practice into how my database
    > works.
    >
    > ... detail snipped ...


    This is basically a "bill-of-materials" type of problem = try Googling
    "BOM". I've done a quite similar thing before, this is the solution I
    worked out if it helps...

    Standard SQL doesn't understand your hierarchy of locations. You need to
    do a recursion which fires a select for each level of the hierarchy in
    turn until there are no locations found. Then you have reached the
    bottom of the deepest hierarchy.

    First create a work table to hold your BOM expansion:
    CREATE TABLE Locn_Tree (
    Level_Nr INT NOT NULL,
    Locn VARCHAR (25) NOT NULL,
    Parent_Locn VARCHAR (25),
    other columns...);

    Next put the node that you want to expand into the work table:
    INSERT INTO Locn_Tree (Level_Nr, Locn, Parent_Locn, ...)
    SELECT 0, L.Locn, L.Parent_Locn, ...
    FROM Locn AS L
    WHERE P.Locn = 'locn';

    And now expand the first level in the hierarchy:
    INSERT INTO Locn_Tree (Level_Nr, Locn, Parent_Locn, ...)
    SELECT 1, L.Locn, L.Parent_Locn, ...
    FROM Locn_Tree AS T
    INNER JOIN Locn AS LP
    ON T.Locn = L.Parent_Locn
    WHERE T.Level_Nr = 0;

    And again for each level in turn until no more rows are INSERTed:
    INSERT INTO Locn_Tree (Level_Nr, Locn, Parent_Locn, ...)
    SELECT (level), L.Locn, L.Parent_Locn, ...
    FROM Locn_Tree AS T
    INNER JOIN Locn AS L
    ON T.Locn = L.Parent_Locn
    WHERE T.Level_Nr = (level - 1);

    Now you have the part of the hierarchy you want expanded in the work
    table, and you can SELECT it in the order you want to present it.

    There's an example here:
    http://www.sqlteam.com/article/more-trees-hierarchies-in-sql

    And example code here for Access:
    http://www.mvps.org/access/queries/qry0023.htm

    Hope this helps,
    MC
    MightyChaffinch, Dec 5, 2008
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?SXJmYW4gQWtyYW0=?=

    Dynamic Controls in asp.net...Building a tree hierarchy..please he

    =?Utf-8?B?SXJmYW4gQWtyYW0=?=, Dec 13, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    488
    =?Utf-8?B?SXJmYW4gQWtyYW0=?=
    Dec 13, 2004
  2. Irfan Akram
    Replies:
    1
    Views:
    149
    Mujtaba Syed
    Dec 13, 2004
  3. FrankEBailey
    Replies:
    2
    Views:
    185
    Anthony Jones
    Feb 18, 2006
  4. Replies:
    8
    Views:
    715
    John Reye
    Apr 26, 2012
  5. Replies:
    5
    Views:
    261
Loading...

Share This Page