it sounds like you should search for "joe celko's nested sets"
i dont know the details but you should check it out...try google or
something (:
----- Original Message -----
From: "Eric Scheid" <[EMAIL PROTECTED]>
To: "Multiple recipients of list witango-talk" <[EMAIL PROTECTED]>
Sent: Tuesday, December 03, 2002 12:30 AM
Subject: Witango-Talk: SQL for recursive self-relationship?
> My knowledge of SQL is scant, so my first question is "is this possible
with
> a single SQL command, or do I need to do lots of loops and recursion in my
> code?" The actual SQL is moot until I know that.
>
> The situation:
>
> I have a table of Topics with fields TopicID, name, description, and
> ParentID. By optionally putting some other TopicID into ParentID I can
build
> a hierarchy of topics (assuming only one parent allowed for any topic).
>
> I have another table of Pages, with fields PageID, URL, and Name; and a
> third table of Occurrences, with fields of OccID, PageID, and TopicID.
>
> I am thus able to build up a catalog of pages, annotated with topics which
> occur on those pages. There are other data fields not germane to this
> discussion.
>
> In practice, if a sub-topic is assigned to a page then the broader term is
> assumed to also be assigned. That is, only one record in the Occurrences
> table, not one for each topic of the chain of topics.
>
> (Tell me now if this isn't a sane structure, please)
>
> What I want to do is find all pages which contain a given topic, or any of
> it's sub-topics. Thus a search for Mammals would also find Dogs, Goats,
> Cats, Great Cats, Lions, Domestic Cats, and Tabby Cats (and any other cats
I
> may have defined).
>
> Is this even possible to do with, say, an inner join within the same
table,
> or am I asking too much of SQL?
>
> Would I need to do a search for the top level topic, then do another
search
> for topics with that topic id in the parent id, then another search again
> finding all the grand-child topics, then the great-grandchildren, and once
> I've collected all the topic id's to then search for all pages that have
any
> of those topic id's? Sounds like a lot of work, a lot of thrashing.
>
> e.
>
> ________________________________________________________________________
> TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED]
> with unsubscribe witango-talk in the message body
________________________________________________________________________
TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED]
with unsubscribe witango-talk in the message body
