Solved

Tree style Query

Posted on 1998-02-25
6
209 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now