Recursive MYSQL Select

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
definitivecoderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

VGRCommented:
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
Howler_FishCommented:
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
snoyes_jwCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

snoyes_jwCommented:
ping
0
snoyes_jwCommented:
0
snoyes_jwCommented:
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
snoyes_jwCommented:
It seems Howler_Fish contributed more than VGR...
0
periwinkleCommented:
It would appear that I seleted the wrong answer - good catch;  I'll modify my recommendation (thanks).
0
snoyes_jwCommented:
Thanks, and thanks for your cleanup work!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.