CODEWITHSUNDEEP
sqlsql-server
Vehlin
Vehlin

Reputation: 57

Parent/Child Tree in Table

I've got a bit of a problem that I'm struggling with. I have a legacy database that represents a tree structure within itself using Parent and Child fields to represent itself. It also contains Previous Peer, Next Peer and Level fields.

The main problem I have is that that database doesn't use NULL to represent the start and end points of the relationships, it uses -1.

What I'm trying to do is get all the child records of a given parent.

ID    Parent    PriorPeer   NextPeer   Child   Level
0       0         -1          -1         1       0
1       0         -1           3         2       1
2       1         -1          -1         4       2
3       0          1           5        -1       1
4       2         -1          -1        -1       3

I've managed to write a CTE

;with temp as (
    select * from source
    where ID = 0

    union all

    select y.* from source y
    inner join temp x on y.parent = x.ID
)

select * from temp

This works fine for all ID values other than 0. But falls apart because the top level's parentID references itself rather than -1, the query just repeats itself endlessly. Is there a way I can break this self reference without changing the table data?

Upvotes: 0

Views: 136

Answers (3)

Peter B
Peter B

Reputation: 24147

You can break the loop by using

inner join temp x on y.parent = x.ID and y.ID > 0

or

inner join temp x on y.parent = x.ID and y.Level > 0

Upvotes: 1

George Menoutis
George Menoutis

Reputation: 7240

Wherever you would use the field Parent, instead use

case when Level=0 then -1 else Parent end as true_parent

Upvotes: 1

Andrew
Andrew

Reputation: 27294

As simple as adding a predicate to block instances where parent == child id?

with temp as (
    select * from source
    where ID = 0

    union all

    select y.* from source y
    inner join temp x on y.parent = x.ID and y.parent <> x.id
)

select * from temp

Upvotes: 0

Related Questions