Nested Set problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Nested Set problem

Author

Topic

wunderkind
Starting Member

5 Posts

Posted - 2003-11-13 : 05:34:14

I've searched all forums without luck.
Now i try it here, hope somebody can help me.

My problem is, i have a nestedset table and i search for e sql-statement that give me the siblings of e.g. 'Donna' back

The result that i want should be, Donna, Eddie, Fred.

Albert (1,12)
|
--------------
|                 |
Bert (2,3) Chuck (4,11)
                   |
--------------------------
|                  |               |
Donna (5,6) Eddie (7,8) Fred (9,10)

Sorry for my bad english, my mother language is german

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-13 : 06:27:05

Could you post the table with some sample data?
And please provide more details.

PS Embrace your table text with two code tags:

<code>
... here table ...
</code>

Instead "<" and ">" should be "[" and "]".

wunderkind
Starting Member

5 Posts

Posted - 2003-11-13 : 07:06:02

ok. here we go.
My table looks like this


id  int(12)  
root_id  int(12)  
name  varchar(50)   
lft  int(12)   
rgt  int(12)  

id |root_id  |name    |lft  |rgt|
-------------------------------------------
1  |1        |Albert  |1    |12
-------------------------------------------
2  |1        |Bert    |2    |3
--------------------------------------------
3  |1        |Chuck   |4    |11
--------------------------------------------
4  |1        |Donna   |5    |6
---------------------------------------------
5  |1        |Eddie   |7    |8
---------------------------------------------
6  |1        |Fred    |9    |10
---------------------------------------------

what i know right now is, donna is a child of chuck.
now i want to know who are the brothers of donna.
It's Eddie and Fred. How must be the sql-statement for that.

i hope now it's a little bit clearer

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-11-13 : 07:20:58

That's a good one. I can get the answer using two queries that I have used before. I need to think further on how to do it with a single query.


declare
    @employee varchar(20),
    @boss varchar(20)
select
    @employee = 'Donna'

select
    @boss = n1.employee
from
    dbo.nestedsetmodel n1
    inner join dbo.nestedsetmodel n2
        on n2.lft between n1.lft and n1.rgt
    inner join dbo.nestedsetmodel t3
        on (n2.lft between t3.lft and t3.rgt and
            t3.lft between n1.lft and n1.rgt)
where
    n2.employee = @employee and
    n1.employee <> @employee
group by
    n1.employee,
    n1.lft
having
    count(*) -1 = 1

select
    n2.employee as siblings
from
    dbo.nestedsetmodel n1
    inner join dbo.nestedsetmodel n2
        on n2.lft between n1.lft and n1.rgt
    inner join dbo.nestedsetmodel t3
        on (n2.lft between t3.lft and t3.rgt and
            t3.lft between n1.lft and n1.rgt)
where
    n1.employee = @boss and
    n2.employee <> @boss
group by
    n2.employee,
    n2.lft
having
    count(*) - 1 = 1
order by
    n2.lft asc

Jay White
{0}

wunderkind
Starting Member

5 Posts

Posted - 2003-11-13 : 07:24:33

I must say that this query should work in a mySql Database

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-13 : 07:50:40

Well.. then a counter-question: what is the mySQL?

wunderkind
Starting Member

5 Posts

Posted - 2003-11-13 : 07:55:43

mySQL is The World's Most Popular Open Source Database.
It use the ANSI SQL 99 syntax.

for more information visit [url]http://www.mysql.com[/url]

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-11-13 : 07:57:36

And SQLTeam is The World's Most Popular Microsoft SQL Server Website.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-13 : 08:00:19

Suppose Donna has one more brother.

But we have only Left and Right "issues".

wunderkind
Starting Member

5 Posts

Posted - 2003-11-13 : 08:51:19

@robvolk

i think thats no problem between mysql und mssql the sql-syntax is near the same.

SqlStar
Posting Yak Master

121 Posts

Posted - 2003-11-13 : 08:58:34

But this web site only for Microsoft SQL Server...:)

":-) IT Knowledge is power :-)"

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-13 : 09:13:13

And how about the third (maybe outlaw) brother?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-11-13 : 09:29:52

I'm glad I could help ?!?

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-13 : 13:03:12

this should returns the parent of each node:

select a.id, max(b.id) as parentID
from
tbl a
inner join
tbl b
on a.id between b.lft and b.rgt
group by a.id

EDIT: See my post below for corrections

so, just join this query to itself on the "parentID" column. All results with the same parentID are siblings.

i.e.,

select a.id as NodeID, b.id as SiblingID
from
(above SQL) a
inner join
(above SQL) b
on a.parentID = b.parentID

something like that. But I am not sure if you can do subqueries like this in MySQL or if you can create views or whatnot. If you can, I would create the first SQL statement in a view and then it is pretty easy to follow.

- Jeff

mkbosmans
Starting Member

15 Posts

Posted - 2003-11-13 : 18:00:28

From MySQL version 4 and above you can do subqueries, but the support voor view and SPs is stil under development.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-11-13 : 18:04:15

quote:

It use the ANSI SQL 99 syntax.

That is nothing to be proud of..

DavidM

"SQL-3 is an abomination.."

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-11-14 : 06:54:42

quote:
Originally posted by jsmith8858

this should returns the parent of each node:

select a.id, max(b.id) as parentID
from
tbl a
inner join
tbl b
on a.id between b.lft and b.rgt
group by a.id

so, just join this query to itself on the "parentID" column. All results with the same parentID are siblings.

i.e.,

select a.id as NodeID, b.id as SiblingID
from
(above SQL) a
inner join
(above SQL) b
on a.parentID = b.parentID

Are a and b two different tables in your first query, or aliases to the same table? What is the id column in relation to the left and right? Jeff, can you clarify what you are doing here and give an example? I've spend a lot of time trying to figure out how to get the nested set model to tell me "who is my direct parent, just one level up?" My query above is the best way I could figure out so far ...

Jay White
{0}

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-14 : 07:37:53

Of course Jeff meant aliases of the same sole table.

What the hell is the nested set model?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-14 : 07:45:05

quote:

What the hell is the nested set model?

Good article on nested set vs adjacency
http://www.intelligententerprise.com/001020/celko.shtml

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-14 : 08:12:09

Yes, same table different alias.

Actually -- i messed up. i implied the "ID" column would be between lft and rgt, when really it's the "lft" column that should be between. With that, there are a few more minor changes I screwed up on. What I posted should then be changed to:

select a.id, max(b.lft) as ParentLft
from
tbl a
left outer join
tbl b
on a.lft > b.lft and a.lft < b.rgt
group by a.id

which returns the LFT of the parent (or NULL now, thanks to the outer join). Link them up and you have siblings. to then get the "ID" or name or whatever of the parent you must do one more join.

So, you get something like:


create table tbl (id varchar(20), lft int, rgt int)
GO

insert into tbl
select 'Albert',1,12 union
select 'Bert',2,5 union
select 'Chuck',6,13 union
select 'Donna',7,8 union
select 'Eddie',9,10 union
select 'Fred',11,12 union
select 'George',3,4
GO

create view tmp
as
select a.id, max(b.lft) as ParentLft, count(b.lft) as Lvl
from
tbl a
left outer join
tbl b
on a.lft > b.lft and a.lft < b.rgt
group by a.id

GO

select * from tmp

--so, to get siblings:

select a.id, b.id as siblingID
from tmp a
inner join tmp b
on
a.parentlft = b.parentlft and
a.id <> b.id
order by a.id, b.id

GO
drop view tmp
drop table tbl

(note: i added one more node to the original poster's example tree, to make it slightly more complex).

not sure if the above code is easier/different than any other methods, but that's how I would get the parent. It should work for all cases, but I may be wrong i haven't thoroughly tested for all situations.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources