Help with parent / child tree query

[da644]

Hi Everyone.

I have a database that has a parent/child structure. Some records in the database have a parent record and some a child record and some have both. What I'm looking to do is create two queries, one where I can specify the child and get the parents and one where I specify the parent and get all the child. The database is structured in the following way.

Fields: recordID, parentID, information
Example: 1, 0, some information
Example: 2, 1, some information
Example: 3, 2, some information
Example: 4, 0, some information
Example: 5, 4, some information
Example: 6, 0, some information

So, what I need to do is specify that the record I want is for example recordID = 3, but I also want to know which records are its parents right back to the root parent. So in this case it would return recordID 3, 2 and 1.

I also want a query to go the other way so I could say get recordID = 1 and its children.

At the moment I'm doing this using a loop with a condition statement, but this means doing as many queries as necessary until you reach the root and this can sometimes but 10 or 20. I would like to do it all in one SQL query.

Any ideas?

Best Regards

Andrew.

 

[piti]

i'm using a tree like database too, but i have a maximum of 4 nested children
to make selects more efficient once a day i create a special table where i put all the records where the structure is as follows:

recordid|parent|recordid_01|parent_01|...|recordid_04|parent_04|topid|topparent

where recordid/parent is the "youngest" child and topid/topparent is he root

so it's very easy selecting all parents of one child and all the children of one root

i use sql like this to select the records from the non structured table before inserting them into the special table:

SELECT tablename.recordID, tablename.parentID,
case when tablename1.recordID is not null then tablename1.recordID else null end, case when tablename1.parentID is not null then tablename1.parentID else null end,
case when tablename2.recordID is not null then tablename2.recordID else null end,
case when tablename2.parentID is not null then tablename2.parentID else null end,
case when tablename3.recordID is not null then tablename3.recordID else null end,
case when tablename3.parentID is not null then tablename3.parentID else null end,
case when tablename4.recordID is not null then tablename4.recordID else null end,
case when tablename4.parentID is not null then tablename4.parentID else null end,
CASE
WHEN tablename4.recordID IS NOT NULL THEN tablename4.recordID
WHEN tablename3.recordID IS NOT NULL THEN tablename3.recordID
WHEN tablename2.recordID IS NOT NULL THEN tablename2.recordID
WHEN tablename1.recordID IS NOT NULL THEN tablename1.recordID
ELSE tablename.recordID
END AS top_recordID,
CASE
WHEN tablename4.parentID IS NOT NULL THEN tablename4.parentID
WHEN tablename3.parentID IS NOT NULL THEN tablename3.parentID
WHEN tablename2.parentID IS NOT NULL THEN tablename2.parentID
WHEN tablename1.parentID IS NOT NULL THEN tablename1.parentID
ELSE tablename.parentID
END AS top_parentID
FROM tablename
LEFT JOIN tablename AS tablename1 ON tablename.parentID = tablename1.recordID
LEFT JOIN tablename AS tablename2 ON tablename1.parentID = tablename2.recordID
LEFT JOIN tablename AS tablename3 ON tablename2.parentID = tablename3.recordID
LEFT JOIN tablename AS tablename4 ON tablename3.parentID = tablename4.recordID

having a table like this makes the selects much easier and quicker

hope that helps

 

[JustKIDn]

I think you're doing this the hard way.

I too am working on something like this. I call mine a Pedigree DB. I'm using it to keep track of our goats.

Here is the basic structure:

tblgoats:
-gRin (PK autonumber)
-gName
-gGender
-gSire (integer)
-gDam (integer)

There are more fields. But these are all that's needed for this demo.

Since ALL parents are also a child. You only need to enter the names once (in gName).

Then just do a query using self joins. (RIGHT JOIN's, and LEFT JOIN's)
I've got 3 generations worked out so far (up and down) and tonight I'll have the 4th generation part of the query done.

I've seen a web page somewhere that shows how to do it.
If anyone is interested in it I can find it later and post it.

It's really easy. And cool the way it works!
One query, and no IF/Then or Case statments.

HTH
tgus

____________________________
Families can be together forever...

 

[piti]

well, i have really many rows with data
after creating the special table that helped me to remove all the joins from the query all the users reported increased speed of the app, so maybe it's the hard way but for me the better way

 

[r937]

andrew, you cannot get an indeterminate number of parents in mysql without looping

you can hard-code a series of joins, but if you have one more level than joins, it doesn't "work" in the sense that it doesn't return enough

piti, nice solution -- you are trading the extra effort of creating a denormalized table for the improved efficiency of certain queries

rudy

 

[da644]

Hi Guys.

Ok, looks like it now possible in MySQL, but as I'm using ColdFusion MX as the scripting language that the query results are be passed to I can do one query to get all the records in the database and then use the CF Query of a Query function to query the full table and get the results I require without doing multiple actual database queries. Because CF queries the query in memory it is much quicker than doing multiple queries and has increased the page load page greatly.

Best Regards

Andrew.

 

[JustKIDn]

I now have a 4 generation query with a series of RIGHT JOIN's and LEFT JOIN's.
I can pick out anyone I want up or down the tree.
In some cases that I know of, I actually have at least 5 or 6 generations, maybe more.

So, even though I'm only collecting 4 generations at any one time, there may be more related data. To get that I just need to select a name in the returned data and run it again with that name as the child and I'll get another 4 (really 3 new) generations. So If I had 7 generations of data on one line, I can see all of it just running the query twice.

I can't speek to speed yet, as I don't have enough data to do speed testing. But I'm guessing that this will work well for our needs for any number of names.

piti, that's preety clever. In a sense you are creating a 'Data Warehouse', and then reading from that.
The downside is, you're reporting against old data.

i.e if you run your create first thing in the morning. Then any reporting in the morning is close to 'real time'. However as the day gets old, so does the data. So that by the end of the day, it's possible to look for a name that should be there, but isn't yet, because it only got entered sometime today.
At this point I may enter the name again (not knowing someone else already has) and tomorrow we have duplicated data. And time lost from duplicated efforts.
Data warehouses are great for reporting on history. Like financials (sales reports) but can cause issues when dealing with 'live' data.
If you have measures in place to handle situations, then your plan can work well. In fact I think it would work well in my situation, because we are not a big business with many people entering (and retreving) data.

If anyone is interested, I'd be willing to share, or help.
tgus

____________________________
Families can be together forever...

 

[piti]

well you're right i know about that real time prob, but my database is partialy view-only for the public (changing record relations and new record insertion is not allowed), so they do not know there's already something new added and will be online next day ;-)
and the fact is - there are not so many insertions since the data is almost complete, i think the last added records is dated june '02, we are now concentrating on the app speed and user friendliness :-D

btw if you found the link to the site you mentioned could you post it?

 

[JustKIDn]

piti,

I had a feeling you already thought of that.

If you're not making daily updates to your DB then you don't even need to run your create table. That means you'll have more time to do other things.

Here's the link

http://users.bigpond.net.au/abrowne1/ser-06.html

Keep in mind, this guy is an Access programmer. So he has written this with Access in mind.

In his example he uses all LEFT JOIN's, but in mine I used RIGHT JOIN's for the first two and LEFT JOIN's for the rest.

Also I have observed that MySQL is case sensitive and doesn't even seem to allow any upper case characters in the table name. (if there is a way around this, please let me know)

Look around this guys website. He has another page that has 'tips'. (you can get at it from his main page) Some of which can be used in other programs. Some are Access only but unless you use Access they won't be of much help to you.

Does this help?

tgus

____________________________
Families can be together forever...

 

[piti]

i left the system like this because i do not insert new data - i am only admin - and do not want to check it everyday

now to your uppercase prob - if you are on win system, that's right, but that is causes by the operating system - windows is case insensitive, so all the other apps are too ;-)
if you use mysql on a unix like system that's a strange behaviour - i use capitals in almost every tablename and it works

 

[JustKIDn]

Yes, I use MySQL windows version and when I use MySQl-CC or MySQL-Front, I try to create a table with an uppercase like tblGoats. It lets me type it but after it has been created, I see the name is all lower case. This also happens if I try to rename it.

I know windoz is not case sensitive. But windoz lets you use upper/lower without changing it.

Are you saying there's a way to over come this?
tgus

____________________________
Families can be together forever...

 

[piti]

i use the winbuild of mysql only when a friend of mine wants to help or setup something and i'm not completely into the details of the way win stores the filenames

that friend wanted the same - have uppercase characters in table names, i managed to do it only by manualy renaming the relevant files, but after some writing to these tables (update, insert) the tablenames were again all lowercase

maybe changing the code of mysql could solve the prob, but i'm only guessing

if not try linux instead ;-)

 

[JustKIDn]

I'd love to try it on Linux. But right now, I'm still working out some Samba and permission issues.

Once I get that done, then I may start working on MySQL on Linux with PHP. So I can do some things for the browser.
Like a recipe DB for my wife, done completely through the browser.
tgus

____________________________
Families can be together forever...