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

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

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.

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,


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/

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

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.