Re: Search in NestedSets

charles_bronson wrote:

> hello everyone...
>
> i hope i am right here with my problem. this is what driving me nuts
> since a long time:
>
> i have 3 tables, one for adresses, one for a structure and one for
> referencing both together. the structure table is a nested sets table.
> everything is fine, reading, checking the tree, insert etc.
>
> so now i want to search in the adresses for a specific one. let's say i
> make a search LIKE '%Schmit%' or whatever. i need help how i can get
> the adress, the structure element that it is in AND the whole tree
> above this element with ONE query. it's driving me nuts. i can't figure
> it out.
>
> by the way the only information that i am storing in the reference
> table is the id of the adress and the id of the structure element that
> it is in. and sure each entry in this table has also a id.
>

You need one of two things. First would be a DMBS that supports the "WITH RECURSE" when you query. If you don't have that, there are two other ways to get there. The first is a "materialized path", where there is a column in the hierarchy table that contains a concatenated string of keys up to the root. To find everything above an element, you find every element that matches your element to the limit of the first element's length.

Mr. -CELKO- will jump in and tell you to buy his books on this. I can't speak for his book on hierarchies, but the SQL for Smarties book has this stuff in it and explains it pretty well. Well enough I never had to go elsewhere. He is a little gruff in the newsgroups though, nobody knows why.

-- 
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth_at_(Sec)ure(Dat)a(.com)