sql select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql select

Author

Topic

rahul123
Starting Member

8 Posts

Posted - 2006-06-04 : 21:22:24

i have a table like this

group_id group_name main_group_id
------------------------------------
1 casinos 0
2 hotels 0
3 resorts 1
4 travels 3

what this table does is, it allows nested groups. so each group can be under another group. like travels is a subgroup under resorts, and resorts under casios. so when i edit casinos whose group_id is 1, I should get hotels (group_id =2) i.e (1,2) combination and for hotels I should get (2,1),2,3) and (2,4).
I am trying to do it in stored procedure. So this stored procedure expects a group_id and returns a collection of group_id's. Thanks in advance for the help.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-04 : 21:32:06

How is hotels connected to casinos? because they have the same main_group_id

>> for hotels I should get (2,1),2,3) and (2,4)
can't see how that happens.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-05 : 01:52:59

If you meant to retreive tree structure, then refer
http://www.nigelrivett.net/RetrieveTreeHierarchy.html

Otherwise post additional informations

Madhivanan

Failing to plan is Planning to fail

rahul123
Starting Member

8 Posts

Posted - 2006-06-05 : 08:05:45

Initially when a group is created it could be independent i.e for hotels, the main_group_id will be 0 and also for group casinos too. So later this group could be added to another group, so then group hotels could be added to casinos...actually it is not 1-1 parent child relation. Thanks in advance for help.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-05 : 08:34:54

rahul123 - your question is a little ambiguous (to me), but it from what you've given, it seems to me you just want to return all the descendants and all the siblings from your tree structure. If so, then something like this will do the trick...

--data
declare @t table (group_id int, group_name varchar(10), main_group_id int)
insert @t
          select 1, 'casinos', 0
union all select 2, 'hotels', 0
union all select 3, 'resorts', 2 --note 2, not 1
union all select 4, 'travels', 3

--input
declare @group_id int
set @group_id = 2

--calculation
declare @descendants table (group_id int)
insert @descendants select @group_id

while @@rowcount > 0
    insert @descendants
    select t.group_id
    from @t t inner join @descendants d on t.main_group_id = d.group_id
    where t.group_id not in (select group_id from @descendants)

select @group_id as input, group_id from (
select group_id from @descendants
union
select group_id from @t where main_group_id =
    (select main_group_id from @t where group_id = @group_id) --siblings
) a
where not @group_id = group_id
order by group_id

/*results
input       group_id    
----------- ----------- 
1           2

or

input       group_id    
----------- ----------- 
2           1
2           3
2           4
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

rahul123
Starting Member

8 Posts

Posted - 2006-06-05 : 09:27:13

sorry for confusing. here is the scenario. initially when a group is created it will/won't be added to another group. so for the groups which are not added to another group will have the main_group_id = 0. for the groups added to another group will be having that group_id as its main_group_id value. so the data suppose say is

group_id group_name main_group_id
----------------------------------
1 casinos 0
2 hotels 0
3 resorts 1
4 travels 3

what it means groups like casinos, hotels are independent initially and "resorts" is subgroup under "casinos" and "travels" under "resorts" group.
so when I edit "casinos" i should get group "hotels" because casinos is main group for resorts directly and travels for indirectly through "resorts" group. so the available group will be "hotels". similarly for group "resorts" when I edit, I should be getting "casinos", "hotels" and not "travels". Thanks in advance for help.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-05 : 09:39:13

rahul123 - I'm afraid that hasn't really cleared anything up for me. How does what I posted compare to what you want?

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

rahul123
Starting Member

8 Posts

Posted - 2006-06-05 : 09:55:44

for input 2, i should get group_ids 1 and for input 1 I should get group_ids 2,3,4. Hope this helps.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-05 : 10:03:16

--data
declare @t table (group_id int, group_name varchar(10), main_group_id int)
insert @t
          select 1, 'casinos', 0
union all select 2, 'hotels', 0
union all select 3, 'resorts', 1
union all select 4, 'travels', 3

--input
declare @group_id int
set @group_id = 1

--calculation
declare @descendants table (group_id int)
insert @descendants select @group_id

while @@rowcount > 0
    insert @descendants
    select t.group_id
    from @t t inner join @descendants d on t.main_group_id = d.group_id
    where t.group_id not in (select group_id from @descendants)

select @group_id as input, group_id from (
select group_id from @descendants
union
select group_id from @t where main_group_id =
    (select main_group_id from @t where group_id = @group_id) --siblings
) a
where not @group_id = group_id
order by group_id

/*results
input       group_id    
----------- ----------- 
2           1

or

input       group_id    
----------- ----------- 
1           2
1           3
1           4
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

rahul123
Starting Member

8 Posts

Posted - 2006-06-05 : 10:13:12

for data

group_id group_name main_group_id
------------------------------------
1 casinos 0
2 hotels 0
3 resorts 1
4 travels 3

and input 1, i should get (1,2)
for input 2, i should get (2,1),(2,3),(2,4)

Thanks in advance for help

rahul123
Starting Member

8 Posts

Posted - 2006-06-05 : 10:21:15

the logic is since 1 is the main_group_id for group_id =3 and 3 is the main_group_id for group_id=4. so when we edit group_id=1 we should be getting group_id = 2. since group_id=1 is main_group_id for 3 directly and indirectly for group_id=3. But I don't know how to do this in stored procedure. thanks in advance for help.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-05 : 10:26:37

Okay, we're homing in on the requirment, I think. So you want anything that is "not a descendant"?...

--data
declare @t table (group_id int, group_name varchar(10), main_group_id int)
insert @t
          select 1, 'casinos', 0
union all select 2, 'hotels', 0
union all select 3, 'resorts', 1
union all select 4, 'travels', 3

--input
declare @group_id int
set @group_id = 1

--calculation
declare @descendants table (group_id int)
insert @descendants select @group_id

while @@rowcount > 0
    insert @descendants
    select t.group_id
    from @t t inner join @descendants d on t.main_group_id = d.group_id
    where t.group_id not in (select group_id from @descendants)

select @group_id as input, group_id
from @t where group_id not in (select group_id from @descendants)

/*results
input       group_id    
----------- ----------- 
1           2

or

input       group_id    
----------- ----------- 
2           1
2           3
2           4
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-05 : 10:34:24

And if you want it in your delimited tuple format (though I don't know why you would), you can do this...

--data
declare @t table (group_id int, group_name varchar(10), main_group_id int)
insert @t
          select 1, 'casinos', 0
union all select 2, 'hotels', 0
union all select 3, 'resorts', 1
union all select 4, 'travels', 3

--input
declare @group_id int
set @group_id = 2

--calculation
declare @descendants table (group_id int)
insert @descendants select @group_id

while @@rowcount > 0
    insert @descendants
    select t.group_id
    from @t t inner join @descendants d on t.main_group_id = d.group_id
    where t.group_id not in (select group_id from @descendants)

declare @s varchar(8000)
select @s = isnull(@s + ',', '') + '(' + cast(@group_id as varchar(10)) 
    + ',' + cast(group_id as varchar(10)) + ')'
from @t where group_id not in (select group_id from @descendants) order by group_id

select @group_id, @s

/*results
1           (1,2)

OR

2           (2,1),(2,3),(2,4)
*/