Solved

Tree style Query

Posted on 1998-02-25
6
215 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:rdominelli
6 Comments
 
LVL 9

Expert Comment

by:cymbolic
ID: 1090384
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
 

Author Comment

by:rdominelli
ID: 1090385
It is possible in other sql dialects such as oracle (oracle syntax is using the connect by keyword)

0
 
LVL 6

Accepted Solution

by:
BenClark earned 100 total points
ID: 1090386
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:rdominelli
ID: 1090387
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
 
LVL 7

Expert Comment

by:simonsabin
ID: 2141595
0
 

Expert Comment

by:adjennin
ID: 2760410
I couldn't get that to work.  It always returns 0.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question