Sample recusrive SQL from another database engine...
level is a built in field. This is very useful and powerful syntax allowing
one to build tree's (ie parent child relationships) inside of a table.
SELECT level, chld
FROM tbl1
START WITH value = 'some value'
CONNECT BY parent = PRIOR(child)
Dennis Cote <[EMAIL PROTECTED]> wrote: Clark Christensen wrote:
> So, finally, the question: What might the SQL look like to retrieve a list of
> predecessors for 'PC1_v1.3'? Sure, I could prepare a stmt in the app (Perl),
> and walk the chain in some fashion. Is this result even possible using plain
> SQL?
>
>
Clark,
SQLite does not support the recursive SQL queries that could be used to
do this kind of processing. So there is no way to follow a chain in SQL.
You can convert the problem into pattern matching by having each record
store the path along the chain in that record. This is really a
variation of the SQL tree problem. I have previously posted a sample
implementation of this materialized (or stored) path method at
http://www.mail-archive.com/[email protected]/msg13225.html
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
Dennis Cote <[EMAIL PROTECTED]> wrote: Clark Christensen wrote:
> So, finally, the question: What might the SQL look like to retrieve a list of
> predecessors for 'PC1_v1.3'? Sure, I could prepare a stmt in the app (Perl),
> and walk the chain in some fashion. Is this result even possible using plain
> SQL?
>
>
Clark,
SQLite does not support the recursive SQL queries that could be used to
do this kind of processing. So there is no way to follow a chain in SQL.
You can convert the problem into pattern matching by having each record
store the path along the chain in that record. This is really a
variation of the SQL tree problem. I have previously posted a sample
implementation of this materialized (or stored) path method at
http://www.mail-archive.com/[email protected]/msg13225.html
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
