sysbase sql query a tree

Question

I have seen a lot of answers to this question, but I still can't figure out how to do it. I'm using Sybase. But I prefer a standard SQL answer.

I have a table that forms a number of tree structures. I would like to get the list of all child nodes given a set of parent nodes. For example take the table below

parent     child
AA          AAB
AA          AAC
AAB         AABA
AAB         AABB
AAB         AABC
AAB         AABD
AAB         AABE
AAB         AABF
AABB        AABBC
AABB        AABBA
AABB        AABBB
AABB        AABBC
BB          BBA
BB          BBB
BBB         BBBA

so basically, I want a query that, given the input AA and BB, it would give me all the entries above. Suppose the above is a directory structure, the result is similar to get me all the subdirectories recursively of one of more root.

As I said, I saw the question asked so many times, but still I couldn't find an answer I could use. There was a few links to outside articles, I read 3 or 4, but I think I can't translate that into SQL. (My sql expertise is rusty a bit).

thank you

@VincentBuck select @@version gives: Adaptive Server Enterprise/12.5.4/EBF 16785 ESD#10/P/Sun_svr4/OS 5.8/ase1254/2159/64-bit/FBO/Mon Nov 2 13:08:08 2009 — Charbel, Nov 11 '11 at 17:23

score 1 · Answer 1 · answered Nov 11 '11 at 11:46

There are many solutions for this question, each has own advantages and disadvantages, and there are not ideal. It's depend on your tasks.

One of this solutions is Transition Closure (http://en.wikipedia.org/wiki/Transitive_closure).

Advantage - very simple queries and performance.

Disadvantages - difficult to maintain and large volume due to denormalisation.

F.e. the are two tables Nodes and Transitions.\ The first table is obvious and the second should contains ALL child of EVERY node include node themself with level zero.

create table Node (
   node_id              integer                        not null,
   name                 varchar(255)                   null,
   constraint PK_NODE primary key (node_id)
)
go
create table Transition (
   node_id              integer                        null,
   child_id             integer                        null,
   levl                 integer                        null
)
go

Lvl - level is useful column in many queries.

For example, you can get all child af any node with simple query:

select 
  c.node_id,
  c.name 
from
  Transition t,
  Node c
where 
  t.node_id = :id
and t.child_id = c.node_id
and lvl > 0

Of course you have to create a stored procedure or trigger to populate and maintain Transition table.

There are other solutions (less expensive in volume but more difficult to update) but you can start with this one.

Other RDBMS such Oracle has built in tree queries support (connect by path f.e.) but this query type has VERY bad performance on large tables.

can you please elaborate, or provide some sql that populate Transition and Node in your example above? in terms of maintenance, I could just create those as temporary tables and drop them after return the result I suppose. In terms of performance, this would be called once a day, so should be acceptable to have it taking a minute or so. — Charbel, Nov 11 '11 at 17:33

score 0 · Answer 2 · answered Nov 11 '11 at 14:52

0

Is I understood, you just want the query for that table.

So, you'll need to do something like this:

select distinct parent, child from table
where parent like '%AA%'
OR parent like '%BB%'

If this is not what you need, please place a comment :)

answered Nov 11 '11 at 14:52

aF.

64,980
43
135
198

sysbase sql query a tree

2 Answers2