Prev: Need Help for Query on Summing Prev Row Value and used for calculation
Next: find action on log file
From: Roy Goldhammer on 3 Aug 2010 07:58
Hello there

have system where each client has owner and there is racursive query i've
created that returns all client childs by using with.

One of my users by mistake entered in child one of previous owners and it
crashed the racursive.

Is there a way in with racursive not get childs who have already been
chosen?


From: Stefan Hoffmann on 3 Aug 2010 10:43
hi Roy,

On 03.08.2010 13:58, Roy Goldhammer wrote:
> One of my users by mistake entered in child one of previous owners and it
> crashed the racursive.
Then you must have a design flaw in your table structure.

> Is there a way in with racursive not get childs who have already been
> chosen?
How can we tell without further information, but a guess: Yes, there
seems to be a way...

btw, it's a recursive or hierarchic structure.


mfG
--> stefan <--
From: Roy Goldhammer on 4 Aug 2010 04:06
Whell Stefan:

I'm using with racursive to do it

My table looks like Clients(ClientID int
, Parent_client int)

which Parent_Client is the father of the client

My table data looks like this:

ClientID, Parent_Client
1, null
2, 1
3, 1
4, 2
5, 8 -- the error here because it cause loop.
6, 5
7, 6
8, 7

is there a way using with racursive not to make loop?
"Stefan Hoffmann" <ste5an(a)ste5an.de> wrote in message
news:%23OEb$oxMLHA.5700(a)TK2MSFTNGP04.phx.gbl...
> hi Roy,
>
> On 03.08.2010 13:58, Roy Goldhammer wrote:
>> One of my users by mistake entered in child one of previous owners and it
>> crashed the racursive.
> Then you must have a design flaw in your table structure.
>
>> Is there a way in with racursive not get childs who have already been
>> chosen?
> How can we tell without further information, but a guess: Yes, there seems
> to be a way...
>
> btw, it's a recursive or hierarchic structure.
>
>
> mfG
> --> stefan <--


From: --CELKO-- on 4 Aug 2010 20:57
This is called an adjacency list model. You will need to add triggers
and/or constraints to the DDL.

Google the Nested sets model; it does not have have these problems.
You can also get a copy of TREES & HIERARCHIES IN SQL for the code and
other methods of modeling tree structures in SQL.

From: Tony Rogerson on 7 Aug 2010 05:08


"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:041651bc-e687-4782-81bc-a5ac9c32018e(a)x21g2000yqa.googlegroups.com...
> This is called an adjacency list model. You will need to add triggers
> and/or constraints to the DDL.
>
> Google the Nested sets model; it does not have have these problems.
> You can also get a copy of TREES & HIERARCHIES IN SQL for the code and
> other methods of modeling tree structures in SQL.
>

The nested sets model requires complex procedural trigger code to maintain,
it also badly suffers from concurrency problems for frequently changing
hierarchies.

--ROGGIE--

 | 
Pages: 1
Prev: Need Help for Query on Summing Prev Row Value and used for calculation
Next: find action on log file