Solved

Tree style Query

Posted on 1998-02-25
6
221 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Trigger 8 38
help converting varchar to date 14 25
getting error while running below query  in sql 2 16
Importing cr/lf as text in sql server 7 17
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database 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…

730 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