Solved

Tree style Query

Posted on 1998-02-25
6
229 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

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 needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

630 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