How to set up query for Thesaurus

Hi everyone,

I'm trying to build a Thesaurus. In my thesaurus i've created the
following tables: terms, btnt (broader terms/narrower terms), rt
(related terms)

The structures of the tables are like this:

--------------------------------
| table: terms |
--------------------------------
| term_id | name |
--------------------------------
1 Europe
2 Holland
3 Amsterdam
4 England
5 London


-------------------------------------------
| table: btnt
-------------------------------------------
| btnt_id | bt | nt |
-------------------------------------------
1 Europe(1) Holland(2)
2 Holland(2) Amsterdam(3)
-------------------------------------------


-------------------------------------------
| table: rt
-------------------------------------------
| btnt_id | rt1 | rt2 |
-------------------------------------------
1 Holland(1) England(3)
2 Amsterdam(2) London(4)
-------------------------------------------

As you can see, with a thesaurus you can make an hierarchy relations
(btnt table) between terms as well as vertical relations (RT table).

I have a search query which will give me a term_id from the table terms.
With this term_id I have a query which will search and collect in the
table btnt for the nt with bt as term_id. But as you can see the nt is
also a bt. I want in the same query a loop so I can query it again with
the new query_results. This will be a contineous proces..

I hope you understand what i've written here, and I hope someone can
help me with this query...

Thanks in advance marc

Re: How to set up query for Thesaurus

"Marc" wrote in message
news:44188262$0$195$58c7af7e@news.kabelfoon.nl...
> I have a search query which will give me a term_id from the table terms.
> With this term_id I have a query which will search and collect in the
> table btnt for the nt with bt as term_id. But as you can see the nt is
> also a bt. I want in the same query a loop so I can query it again with
> the new query_results. This will be a contineous proces..

If you're trying to do a query to get all descendants of a given term, then
this is something hard to do with standard SQL with the schema you've
described. Oracle and some other RDBMS have some proprietary extensions for
doing recursive queries, but MySQL does not.

There are techniques for representing heirarchies in a queryable form, for
instance by recording all the paths in the tree. This requires at least one
more table to record these paths.

You would probably benefit from reading Joe Celko's book "Trees and
Heirarchies in SQL for Smarties".
http://www.amazon.com/gp/product/1558609202/

Regards,
Bill K.

Re: How to set up query for Thesaurus

Bill Karwin schreef:
> "Marc" wrote in message
> news:44188262$0$195$58c7af7e@news.kabelfoon.nl...
>
>>I have a search query which will give me a term_id from the table terms.
>>With this term_id I have a query which will search and collect in the
>>table btnt for the nt with bt as term_id. But as you can see the nt is
>>also a bt. I want in the same query a loop so I can query it again with
>>the new query_results. This will be a contineous proces..
>
>
> If you're trying to do a query to get all descendants of a given term, then
> this is something hard to do with standard SQL with the schema you've
> described. Oracle and some other RDBMS have some proprietary extensions for
> doing recursive queries, but MySQL does not.
>
> There are techniques for representing heirarchies in a queryable form, for
> instance by recording all the paths in the tree. This requires at least one
> more table to record these paths.
>
> You would probably benefit from reading Joe Celko's book "Trees and
> Heirarchies in SQL for Smarties".
> http://www.amazon.com/gp/product/1558609202/
>
> Regards,
> Bill K.
>
>
Hi Bill,

I've ordered the book. Thank you for your quick response!

Regards,

Marc

Re: How to set up query for Thesaurus

On Wed, 15 Mar 2006 15:27:38 -0800, in mailing.database.mysql "Bill
Karwin"
wrote:

>| "Marc" wrote in message
>| news:44188262$0$195$58c7af7e@news.kabelfoon.nl...
>| > I have a search query which will give me a term_id from the table terms.
>| > With this term_id I have a query which will search and collect in the
>| > table btnt for the nt with bt as term_id. But as you can see the nt is
>| > also a bt. I want in the same query a loop so I can query it again with
>| > the new query_results. This will be a contineous proces..
>|
>| If you're trying to do a query to get all descendants of a given term, then
>| this is something hard to do with standard SQL with the schema you've
>| described. Oracle and some other RDBMS have some proprietary extensions for
>| doing recursive queries, but MySQL does not.
>|
>| There are techniques for representing heirarchies in a queryable form, for
>| instance by recording all the paths in the tree. This requires at least one
>| more table to record these paths.
>|
>| You would probably benefit from reading Joe Celko's book "Trees and
>| Heirarchies in SQL for Smarties".
>| http://www.amazon.com/gp/product/1558609202/

There are also these on-line articles:
http://dev.mysql.com/tech-resources/articles/hierarchical-da ta.html
http://www.sitepoint.com/article/hierarchical-data-database
------------------------------------------------------------ ---
jnorthau@yourpantsyahoo.com.au : Remove your pants to reply
------------------------------------------------------------ ---