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