RE: parent, child - Patino, Hernando - com.mysql.lists.win32

9 messages in com.mysql.lists.win32RE: parent, child

From	Sent On	Attachments
Leang Pechakrith Titya	22 Sep 2004 07:14
Jordan Sparks	22 Sep 2004 07:40
David Blomstrom	22 Sep 2004 10:46
SGr...@unimin.com	22 Sep 2004 11:58
Jordan Sparks	22 Sep 2004 12:43
Jordan Sparks	22 Sep 2004 12:59
Patino, Hernando	22 Sep 2004 13:26
David Blomstrom	22 Sep 2004 13:36
Patino, Hernando	22 Sep 2004 13:51

Subject:	RE: parent, child
From:	Patino, Hernando (hern...@spcorp.com)
Date:	09/22/2004 01:51:45 PM
List:	com.mysql.lists.win32

The solutions (I know) are:

1. Get a database that supports "pure" Object Oriented Data. Not many around, and some expensive 2. Get a database that supports "hierarchical queries" Oracle does it. But Expensive (and since you are in this newsgroup you do not want this suggestion) 3. Use traditional SQL to retrieve data: (as explained before or in the reference provided) 3.a. Recursive iteration to obtain ancestors or descendants 3.b. Change the structure of the database 4. Use a client software for this need 4.a. Microsoft MSShape OLEDB provider can be used by any Windows programming language or interface. Is pretty simple to use. (see reference at: http://support.microsoft.com/default.aspx?scid=kb;EN-US;189657 also http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/ mdrefmsdatashape.asp )

For option 4, I do not know if we finally have a decent OLEDB for mySQL. You could try it with mySQL ODBC, but I am not sure about the results.

Hernando

-----Original Message----- From: David Blomstrom [mailto:davi...@yahoo.com] Sent: Wednesday, September 22, 2004 4:37 PM To: win...@lists.mysql.com Subject: RE: parent, child

Thanks for the tips, both of you. It will probably be a while before I'm ready to tackle the more advanced hierarchical formula, but that should be my long range goal. I've also learned of some taxonomic software programs that I'll have to check out.

Lots to learn!

--- Jordan Sparks <jspa...@free-dental.com> wrote:

One more thing. It's essentially the same as:

SELECT animals1.AnimalName,animals2.Parent FROM animals AS animals1,animals AS animals2 WHERE animals1.Parent=animals2.AnimalName

Except that if an animal doesn't have a parent, the above query will exclude that animal. The query below will include that animal, and the parent name will be null.

Jordan Sparks

* * * * * * * * * *

Hernando Patino wrote:

I you are working with taxonomy data, most likely you will end up working with hierarchical relationships (variable nodes) rather than plain relational data. Although mySQL does not yet support SQL hierarchical queries there are workarounds as the ones already explained in the mailing list.

For a nice discussion about this type of queries and retrieving data stored in a relational database, please check:

http://www.sitepoint.com/article/hierarchical-data-database

The article is titled: Storing Hierarchical Data in a Database by Gijs Van Tulder

This article will give a better solution that can be implemented in mySQL. However, is a little bit complicated to do maintenance, but the taxonomy data does not change every minute, so it won't be difficult to use that approach.