October 2, 2003 at 9:00 pm
I have a database table, called "activity" with columns ActivityId, Name, ParentId. The following records are in the table:
ActivityIdNameParentId
---------- ---- --------
1 Golf 0
2 Golf1 1
3 Spa 0
4 Golf2 1
2 Spa1 3
The ParentId is an indicator that an activity is a sub-activity of another activity with an appropriate ActivityId, i.e. Golf1 & Golf2 are sub-activities of Golf. ParentId=0 means that this activity is a "parent" and might have "child" activities. I need to build a query, that will pull the records with a "parent" activity followed by its "children", if any, like in this example:
1,Golf,0
2,Golf1,1
4,Golf2,1
3,Spa,0
5,Spa1,3
I tried different scenarios with GROUP BY and ORDER BY and was unsuccessful so far. Any ideas if this is possible at all? Thanks in advance.
Edited by - levv on 10/02/2003 9:01:03 PM
Edited by - levv on 10/02/2003 9:01:27 PM
Edited by - levv on 10/02/2003 9:01:53 PM
October 3, 2003 at 1:34 am
hi, i did the following :-
create table activity
(activityid int, activity_name varchar(10), parentid int)
insert into activity values (1,"Golf",0)
insert into activity values (2,"Golf1",1)
insert into activity values (3,"Spa",0)
insert into activity values (4,"Golf2",1)
insert into activity values (5,"Spa1",3)
and used the following SQL. The csae statement generates a surrogate order key by taking the activityid if there is no parent , or the parentid if it is a child activity.
select a.activityid, a.parentid ,a.activity_name,
case parentid
when 0 then activityid
else parentid
end 'order_key'
from activity a
order by order_key
HTH
Paul
October 3, 2003 at 9:12 am
Paul,
Thanks a lot! Works like a charm, great solution. Thanks again!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply