Design

  • 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

  • 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

  • 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

    steve@dkranch.net

  • 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

  • yes, sorrry. my typo

    Steve Jones

    steve@dkranch.net

  • 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