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:26:28 PM
List:	com.mysql.lists.win32

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

Regards

Hernando Patino, MD

-----Original Message----- From: Jordan Sparks [mailto:jspa...@free-dental.com] Sent: Wednesday, September 22, 2004 3:59 PM To: win...@lists.mysql.com Subject: RE: parent, child

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.

-----Original Message----- From: Jordan Sparks [mailto:jspa...@free-dental.com] Sent: Wednesday, September 22, 2004 12:44 PM To: 'win...@lists.mysql.com' Subject: RE: parent, child

Read the mysql cookbook. No, I don't think it's online. It will really help you with all these types of questions.

SELECT animals1.AnimalName,animals2.Parent (selects Leopard,Carnivores) FROM animals AS animals1 LEFT JOIN animals AS animals2 ON animals1.Parent=animals2.AnimalName (ON Leopard.Parent(Felidae)=Felidae.AnimalName(Felidae))

-----Original Message----- From: David Blomstrom [mailto:davi...@yahoo.com] Sent: Wednesday, September 22, 2004 10:46 AM To: win...@lists.mysql.com; jspa...@free-dental.com Subject: RE: parent, child

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

SELECT parent1.child,parent3.parent FROM parent AS parent1 LEFT JOIN parent AS parent2 ON parent1.parent=parent2.child LEFT JOIN parent AS parent3 ON parent2.parent=parent3.child

This sounds like something I was seeking a solution for weeks ago. Unfortunately, I'm a little confused by the explanation. Could someone translate the script above into a script that would work with my animals database?

Here are the names I'm working with:

table = "animals" child column = "AnimalName" parent column = "Parent"

However, note that an "AnimalName" can be both a child and a parent. For example, Felidae - the cat family - appears in the child column, with Carnivora (the carnivores) opposite it in the parent column. However, Felidae also appears in the parent column, opposite leopard, in the child column.

So let's imagine the following "animal path":

Mammals > Carnivores > Felidae > Leopard

If I'm working on the Leopard page, I can easily grab its parent, Felidae. But how can I display its grandparent or great grandparent - Carnivora, Mammals?

It looks to me like the answer is in the script you provided, but I'm a little confused. Thanks!

Jordan Sparks

-----Original Message----- From: Leang Pechakrith Titya [mailto:ltit...@singnet.com.sg] Sent: Wednesday, September 22, 2004 7:15 AM To: win...@lists.mysql.com Subject: parent, child

Hi Everyone!

I've a table parent(child, parent), where child is the child name and parent is the parent name. I wonder what query I can use to list the (child, great grandparent) pair. Thanks a lot. I'm a sql newbie so it puzzles me tremendously :-(

Cheers,

Tom

-- MySQL Windows Mailing List For list archives: http://lists.mysql.com/win32 To unsubscribe: