April 3, 2002 at 6:27 am
How do u design tables to accomodate the following:
A Division with many subdivisions. (Each sub division may further have its own sub sub division and so on...)
Example
01 Root
001 Sub Root No 1
0001 SubSubRoot No 1
0002 SubSubRoot No 2
002 Sub root No 2
0002 SubSubRoot No 1
00002 SubSubSubRoot No 1
Edited by - madhu_r on 04/08/2002 10:50:39 PM
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply