My/Pg-SQL: How to select ...

[Anon]

Given the following table:

TABLE relation {
INT idA;
INT idB;
}

Populated as follow:
idA idB

---

1 2
2 2
2 4
3 4

How should the SELECT statement in MySQL and PgSQL be written to select idB's "root id(s)". For example, if idB=4, then the root id(s) are 1 and 3 (4<-2<-1 and 4<-3). There will at most be a hierarchy of 3 levels (e.g., 4<-2<-1). I'd like to do this in a single select.

[kparker]

You're not going to be able to do that with a single select unless the dbms supports recursive queries, which neither of these two do.

[smarlowe]

postresql can do it in one statement, but it's ugly and non-recursive (you COULD write a function on plsql to do this recursively, but the sql in postgres doesn't do recursion natively.)

Here's one that works for two levels, I'll leave it to you to add the third possible level yourself:

select ida, idb from relation where idb in (select ida from relation where idb=4) union select ida, idb from relation where idb=4;