Thread: Hierarchies

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    12
    Rep Power
    0

    Hierarchies


    Just a quick question...

    I have this structure:

    Level1:
    id | description
    -------------------
    9 | We're at the Level 1 level

    Level2:

    id | level1 | description
    -----------------------------
    1 | 9 | Now at level 2

    Level3:

    id | level2 | description
    ------------------------------
    4 | 1 | Level 3 is here

    So, essentially, Level3->Level2->Level1 ...

    This is fine, I could do:

    SELECT l1.id from level1 l1, level2 l2, level3 l3 WHERE (l1.id = l2.level1) AND (l2.id = l3.level2);

    This is fine. But what if there is no level3, yet I still want the level1.id which corresponds to level2? Ie. If not all the levels are filled out, but I still want the hierarchy between the levels that exist. The above query will not work obviously....

    Thank you very much.

    Ogden
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    soundslike you need some relational algebra division bidniss...
    you could use NOT EXISTS to find out if an entry in level one has a corresponding key in level 2, etc.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    12
    Rep Power
    0
    Originally posted by metaBarf
    soundslike you need some relational algebra division bidniss...
    you could use NOT EXISTS to find out if an entry in level one has a corresponding key in level 2, etc.
    How do you mean?

    I mean I want the level3's associated with level2, and the level2 associated with level1. However, if there isn't a level3, I would still like the level2's which are associated with the level1....

    Thank you
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    CREATE TABLE level1 (id, description)
    CREATE TABLE level2 (
    id,
    level1,
    description)
    CREATE TABLE level3 (id,
    level2,
    description)

    This is fine, I could do:

    SELECT l1.id from level1 l1, level2 l2, level3 l3 WHERE (l1.id = l2.level1) AND (l2.id = l3.level2);


    It seems like there should be a better way to do it than what I have below, but that's the most straightforward way of doing it that I thought of. I think I am confused about what you're trying to get back from a query. There may be a join method that would enable this sort of level comparison quite simply, but I am not that familiar with the joins logic, however NOT EXISTS can be used in a situation where you're saying "give me ALL of something that meet some condition" so you're saying you want ALL of something from either level1 if it's available and level2 if the level1 is not. Take it from there.

    Code:
    SELECT l1i.id 
    FROM level1 l1i, level2 l2i, level3 l3i
    WHERE IN (
                      SELECT l2ii.level1
                       FROM level2 l2ii
                     )
    INTERSECT (
    SELECT l2iii.id 
    FROM level2 l2iii, level3 l3iii
    WHERE NOT EXISTS (SELECT l1iv.id
                                     FROM level1 l1iv, level2 l2iv 
    WHERE l1iv.id = l2iv.level1)
    AND EXISTS (SELECT l3v.id
                         FROM level2 l2v, level3 l3v
                         WHERE l2v.id = l3v.level2)
    ) --end INTERSECT;
    Last edited by metaBarf; October 21st, 2003 at 08:25 PM.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    use LEFT OUTER JOIN

    and to simplify the design greatly, put all three levels into one table

    create table levels
    ( id smallint not null
    , description char(10)
    , parentid smallint null
    )

    insert into levels (9, 'We''re at the Level 1 level', null)
    insert into levels (1, 'Now at level 2', 9)
    insert into levels (4, 'Level 3 is here', 1)
    insert into levels (5, 'another Level 3', 1)
    insert into levels (6, 'yet another Level 3', 1)
    insert into levels (8, 'back to level 1', null)
    insert into levels (7, 'this level 2 has no 3', 8)

    to pull out the tree to three levels,

    Code:
    select L1.id
         , L1.description
         , L2.id
         , L2.description
         , L3.id
         , L3.description
      from levels L1
    left outer
      join levels L2
        on L1.id = L2.parentid
    left outer
      join levels L3
        on L2.id = L3.parentid
     where L1.parentid is null
    note that the query starts only at the "root" nodes of the tree, i.e. those nodes which do not have a parent

    rudy
    http://r937.com/
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    12
    Rep Power
    0
    Originally posted by r937
    use LEFT OUTER JOIN ..
    metaBarf and r937: Thank you very much for helping me on this.

    Just a question to r937. Unfortunately, I cannot change the structure of the tables (I was just put into this project). How can I do it with tables as level1, level2, level3, etc, using a JOIN?

    Thank you very much

    Ogden
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    substitute your three table names into my query at the appropriate places and make sure the linking colulmn names are the right ones
  14. #8
  15. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    12
    Rep Power
    0
    Originally posted by r937
    substitute your three table names into my query at the appropriate places and make sure the linking colulmn names are the right ones
    Ahh, I had tried that immediately after writing the post and it had worked. Thank you very much. I recall your posts before too in other threads which have helped me. Thank you very much. I really appreciate it.

IMN logo majestic logo threadwatch logo seochat tools logo