| Subject | [firebird-support] Re: Efficiently find records from a self join without children records or a field=1 |
|---|---|
| Author | Svein Erling Tysvaer |
| Post date | 2009-06-23T15:01:37Z |
Hi Erik!
First, when asking 'another question', please start a new topic.
What you're looking for is common table expressions (WITH RECURSIVE),
something that became possible with Firebird 2.1. Look at the release
notes and search for DEPT_TREE.
HTH,
Set
Erik De Laet wrote:
First, when asking 'another question', please start a new topic.
What you're looking for is common table expressions (WITH RECURSIVE),
something that became possible with Firebird 2.1. Look at the release
notes and search for DEPT_TREE.
HTH,
Set
Erik De Laet wrote:
> I second this reply ... this indeed is a very efficient query. One that I can use in my current project also.
>
> But another question: does anyone has a query or stored proc which will list all childs (not only the immediate ones but all of them) of a given parent in a self referencing table with Id and PId ?
>
> Thanks in advance,
> Erik
>
> --- In firebird-support@yahoogroups.com, "Bhavbhuti Nathwani" >
>> Yes, Milan, this was a very efficient query you suggested!!! Even on my tiny set of less than 20 records my query took 0.110 secs. and your suggestion took it down to 0.016 secs. with a lot of improvement in memory and other usages.