April 4, 2002 at 12:31 pm
You have to include field "PARENT" where you can put ID of the previous Root or subroot or if it's Main root - it's own ID
April 4, 2002 at 1:09 pm
We do this with categories here at SSC.
create table Category
( categoryid int
, categoryname varchar(20)
, parentcatid int
)
insert category 1, 'SQL 2000', 0
insert category 2, 'Programming', 0
insert category 3, 'Administration', 1
insert category 4, 'Backup', 1
insert category 5, 'Basic T-SQL', 2
insert category 6, 'Advanced T-SQL', 2
select p.categoryname 'parent'
, c.categoryname
from category p
inner join category c
on c.parentcatid = c.categoryid
you'd have to repeat the self join for each sub level.
Steve Jones
April 4, 2002 at 6:39 pm
Hi, I just tried in my sql analyzer.
Should the last line of the code be:
'inner join category c on
c.parentcatid = p.categoryid' ?
I tried c.parentcatid = c.categoryid, it returns 0 row.
By the way, I use the following code and get the same result:
select p.categoryname as parent, c.categoryname as sub_category
from category p, category c
where c.parentcatid = p.categoryid
Is there any difference between using where clause to join and inner join, in terms of
performance, etc?
Thanks.
Abby Zhang
April 4, 2002 at 8:44 pm
April 4, 2002 at 8:56 pm
Thanks a million folks!
Madhu
Viewing 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply