?
Solved

Recursive MYSQL Select

Posted on 2003-03-27
12
Medium Priority
?
1,235 Views
Last Modified: 2011-10-03
I have a MYSQL db that holds all of my family information. In the db, I'm tracking parent/child relationships as well as husband/wife relationships. Here are the relevant columns in my table for this question:
TABLE NAME - PERSON
P_ID
P_FIRSTNAME
P_BIRTHDAY
P_PARENT_ID
P_SPOUSE_ID

I've created a PHP calendar on which I want to show family member's birthdays. The thing is, I only want to see birthdays for family members where the relation is not too distant (as much as I love my third and fourth cousins, I don't think I care much about their b-days). The site requires a login so at any given time I have the P_ID for the person logged in.

I want to select the following relationships for any person that logs in.
1.) My birthday
2.) My children's birthdays
3.) My children's children's birthdays (grandkids)
4.) My parents
5.) My parent's children (my siblings)
6.) My parent's children's children (my nieces & nephews)

I know I could jimmy-rig this and just run one select to get my kids. Then in PHP, loop through those results and for each child run another select to get their children. Then do the same thing using my parents as the starting point. I am looking for a better MYSQL query than this. I think I can be done in one query, or two at the most (one for me, my kids, and grandkids; and one for parents, their kids and grandkids).

To simplify matters, really all I need to find out is given a P_ID, how can I find that P_ID's children and grandchildren?

If I start my select
SELECT P_ID, P_FIRSTNAME, P_BIRTHDAY
FROM PERSON
WHERE P_PARENT_ID = $myId
(this gets all my kids. now I need to do some kind of 'foreach' of my kids, find their kids)

I hope this is enough details. Thanks in advance
0
Comment
Question by:definitivecoder
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 15

Expert Comment

by:VGR
ID: 8221227
sorry, I can't help you, I'm doing exactly this and I haven't finished myself sorting things out 8-)
I'm building a family site ;

I'm using currently a "users" table, and "relationship tables" for relations "ischildof","ismarriedwith" and those two are the only ones necessary to build a complete family tree. (althought I may add "issiblingof", etc to ease data analysis)

FYI, I solved this problem of birthdays (and other thingies) by offering people logged-in to subscribe to birthdays, either individual per individual, or by surname, or by family.

best regards,
0
 
LVL 1

Assisted Solution

by:Howler_Fish
Howler_Fish earned 100 total points
ID: 8222129
Hi,

I think you need to create a second table that holds the relationship between people.  Effectively I have many relationships, just as many people have many relationships to me.  This new table could look something like

From_P_ID  
To_P_ID
Relationship

Assuming I had a P_ID of 1, and my two children were P_ID 2 and 3.   The table data would look something like:

1 2 Father
1 3 Father
2 1 Son
3 1 Daughter

You should then be able to run a select along the lines of

SELECT A.P_ID, A.P_FIRSTNAME, A.P_BIRTHDAY
FROM PERSON A, RELATIONSHIP B
WHERE B.FROM_P_ID = $myID
AND A.P_ID = B.TO_P_ID
AND B.RELATIONSHIP IN ('Father','Brother','Sister')

You'd want to refine the details a bit better, but it should give you roughly what you are after.  The other problem is that if your cousin ever became your step-father you would potentially return him twice, but at that point I think that would be the least of your worries.... :P


HF
0
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 100 total points
ID: 8225502
Here's one (kinda kludgy) way:

$myParent = SELECT p_parent_id FROM person WHERE p_id = $myId;

SELECT p2.* FROM person p1
LEFT JOIN person p2 on p1.p_id = p2.p_parent_id
WHERE p2.p_id IS NOT NULL
AND (p1.p_parent_id = $myId OR p1.p_id = $myId OR p1.id = $myParent OR p1.p_parent_id = $myParent);

If you just want the children and grandchildren and not siblings and niece/nephew, take out the (p1.id = $myParent OR p1.p_parent_id = $myParent)

You might have to add a DINSTINCT or a GROUP BY to avoid duplicate entries.  How do you deal with the fact that everybody has two parents, but you have only one parent id?  You might create a father_id and a mother_id.
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 33

Expert Comment

by:snoyes_jw
ID: 8432352
ping
0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 8432398
0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 8519726
No comments in a while.  Did you find an answer?  If you solved it yourself, be sure to post in the Community Support area for a delete/refund.
0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 12243731
It seems Howler_Fish contributed more than VGR...
0
 
LVL 15

Expert Comment

by:periwinkle
ID: 12260678
It would appear that I seleted the wrong answer - good catch;  I'll modify my recommendation (thanks).
0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 12260792
Thanks, and thanks for your cleanup work!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question