October 21st, 2003, 02:28 PM
-
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
October 21st, 2003, 03:16 PM
-
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.
October 21st, 2003, 03:28 PM
-
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
October 21st, 2003, 08:09 PM
-
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.
October 21st, 2003, 10:20 PM
-
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/
October 21st, 2003, 10:43 PM
-
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
October 21st, 2003, 10:47 PM
-
substitute your three table names into my query at the appropriate places and make sure the linking colulmn names are the right ones
October 21st, 2003, 11:16 PM
-
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.