470,636 Members | 1,566 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,636 developers. It's quick & easy.

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
Mar 15 '06 #1
3 3289
"Marc" <sp**@maju.nl> wrote in message
news:44*********************@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.
Mar 15 '06 #2
Bill Karwin schreef:
"Marc" <sp**@maju.nl> wrote in message
news:44*********************@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
Mar 16 '06 #3
On Wed, 15 Mar 2006 15:27:38 -0800, in mailing.database.mysql "Bill
Karwin" <bi**@karwin.com>
<dv*********@enews1.newsguy.com> wrote:
| "Marc" <sp**@maju.nl> wrote in message
| news:44*********************@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/...ical-data.html
http://www.sitepoint.com/article/hie...-data-database
---------------------------------------------------------------
jn******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------
Mar 16 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Hans Malherbe | last post: by
2 posts views Thread by hammad | last post: by
2 posts views Thread by iyhammad | last post: by
reply views Thread by Griff | last post: by
3 posts views Thread by moondaddy | last post: by
8 posts views Thread by johnerics | last post: by
reply views Thread by Calvin Spealman | last post: by
1 post views Thread by Korara | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.