Is this possible? Recursive data structure...

13 Jul 2006 4:00 PM

zanthor

I am designing a document management system and wanted to impliment
groups similar to what you would find in Active Directory...

Basically I want to query a table and have the SQL server go through
the results, and based on a value returned per row either output that
row to a view, or get the documents associated with the group and
output them to the view...

Psuedocode:

$results = query(select type, docid, doctableid from tblsubdetails)

function getrecords($results)
    foreach record in $results
        if type = 0 then
            output docid, doctableid // Ouput these to a temp table or a view or
whatever
        else
            // Type 1 means it's a group
            $other_results = query(select type, docid, groupid from
tblSubGroupDetails where groupid = docid)
            getrecords($other_results)
        end
    end
end

The reason behind this is I am working on a database that must keep
it's API compatable with our existing intranet site - so I need a view
that is queryable by doctableid.

Thanks,

Will

13 Jul 2006 4:06 PM

Arnie Rowland

What version of SQL Server are you using? (2000 or 2005)

--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."

<zant***@gmail.com> wrote in message

Show quote

news:1152806438.364047.65110@i42g2000cwa.googlegroups.com...
>I am designing a document management system and wanted to impliment
> groups similar to what you would find in Active Directory...
>
> Basically I want to query a table and have the SQL server go through
> the results, and based on a value returned per row either output that
> row to a view, or get the documents associated with the group and
> output them to the view...
>
> Psuedocode:
>
> $results = query(select type, docid, doctableid from tblsubdetails)
>
> function getrecords($results)
> foreach record in $results
> if type = 0 then
> output docid, doctableid // Ouput these to a temp table or a view or
> whatever
> else
> // Type 1 means it's a group
> $other_results = query(select type, docid, groupid from
> tblSubGroupDetails where groupid = docid)
> getrecords($other_results)
> end
> end
> end
>
> The reason behind this is I am working on a database that must keep
> it's API compatable with our existing intranet site - so I need a view
> that is queryable by doctableid.
>
> Thanks,
>
> Will
>

13 Jul 2006 4:24 PM

zanthor

Server 2003
SQL Server 2005

Sorry, meant to include that!

--Will

Arnie Rowland wrote:
Show quote

> What version of SQL Server are you using? (2000 or 2005)
>
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
>
>
> <zant***@gmail.com> wrote in message
> news:1152806438.364047.65110@i42g2000cwa.googlegroups.com...
> >I am designing a document management system and wanted to impliment
> > groups similar to what you would find in Active Directory...
> >
> > Basically I want to query a table and have the SQL server go through
> > the results, and based on a value returned per row either output that
> > row to a view, or get the documents associated with the group and
> > output them to the view...
> >
> > Psuedocode:
> >
> > $results = query(select type, docid, doctableid from tblsubdetails)
> >
> > function getrecords($results)
> > foreach record in $results
> > if type = 0 then
> > output docid, doctableid // Ouput these to a temp table or a view or
> > whatever
> > else
> > // Type 1 means it's a group
> > $other_results = query(select type, docid, groupid from
> > tblSubGroupDetails where groupid = docid)
> > getrecords($other_results)
> > end
> > end
> > end
> >
> > The reason behind this is I am working on a database that must keep
> > it's API compatable with our existing intranet site - so I need a view
> > that is queryable by doctableid.
> >
> > Thanks,
> >
> > Will
> >

13 Jul 2006 5:52 PM

Anith Sen

Modeling hierarchies in t-SQL involves a bit more work than using simple
recursive functions. In SQL 2005, the introduction of Common Table
Expressions may help with simple recursion based queries.

For some basic ideas, refer to:
http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp

--
Anith

13 Jul 2006 6:08 PM

--CELKO--

Get a copy of TREES & HIERARCHIES IN SQL for several ways to model it.
Also Google "Nested Sets" as an alternative to hidden cursors and
procedural code.

13 Jul 2006 8:37 PM

zanthor

Thank you all, these links have gotten me well on my way to where I
need to be.