Tree style Query

Table 1 has 2 columns
object     group

object can contain user id's or groups.  I would like to build an sql statement which will recursively query the system for all the groups that a given user id belongs to and the groups groups.

Sample data
object
user1     groupa
groupa    groupb
groupb    groupc
groupc    groupa
groupc    groupd

I would like a query which returns

groupa
groupb
groupc
groupd

for user1
rdominelliAsked:
Who is Participating?
 
BenClarkConnect With a Mentor Commented:
As for as I know it can't be done in any DBMS.
However, the next best thing can be done.
Create the temp table to hold the results.
Create a stored procedure to recieve a User Id and get the
group for it and insert it into the temp table. The stored
procedure will then return the new group.
The function that called the stored procedure checks the
return to see of a group was returned.
If so, call the stored procedure again with the group that was
returned. Continue calling the stored procedure until it
does not return a group.
At this point the temp table will contain the data you want.
Then you Select * from the temp table.
Hope this helped. Let me know if you need more specific help.
ebcl@chevron.com
0
 
cymbolicCommented:
You can't do it directly in TSQL.  SQL does not understand recursion, even if you could force a statement out in TSQL.

The best we've been able to do with like data structures is one of two things:

1) read the whole thing in, and rearrange the resultset in code

2) using the Treeview control (from VB5), you can easily attach items and specify their parent in the keys as you add them. Then the control itself will display all items in a heirarchy list for you to use as controlling events, or just for viewing the structure.

Of course, if you have a group  inside itself, who knows what will happen!

I can send you examples of using the Treeview control to show these related structures if you are interested.  Just ask!  
0
 
rdominelliAuthor Commented:
It is possible in other sql dialects such as oracle (oracle syntax is using the connect by keyword)

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
rdominelliAuthor Commented:
Actually I have found my answer.

The key to this question was to use a recursive stored procedure.

Assuming we have a table called rel_table with the following columns
child_id int
parent_id int

The following stored procedure will return the result set I am looking for

if exists (select * from sysobjects where id = object_id('dbo.sp_inner_tree1') and sysstat & 0xf = 4)
      drop procedure dbo.sp_inner_tree1
GO

create procedure sp_inner_tree1 @child_id int

AS
      declare @parent_id int
      declare @print_string char(40)
      declare @returnval int
      select @parent_id = 0
OOK:
if exists(select parent_id from rel_tree where child_id = @child_id and parent_id > @parent_id)
begin
            
            /*select @print_string = ' CHILD ' + convert(char(10), @child_id)*/
            /*            print @print_string */
            select @parent_id = Min(parent_id) from rel_tree where child_id = @child_id and parent_id > @parent_id
            select @print_string = convert(char(12), @parent_id)
            print @print_string
            exec  sp_inner_tree1 @parent_id
            /* select parent_id from rel_tree where child_id = @child_id             */
      
end
else return 0
goto OOK
GO

This will only work to 16 levels deep (or whatever the nested queries level is set to on your box)

Thanks for the help

rich
0
 
simonsabinCommented:
0
 
adjenninCommented:
I couldn't get that to work.  It always returns 0.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.