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
