[onefix]

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

[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.

[onefix]

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

[metaBarf]

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.

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;

[r937]

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,

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/

[onefix]

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

[r937]

substitute your three table names into my query at the appropriate places and make sure the linking colulmn names are the right ones

[onefix]

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.