Recursive MYSQL Query

[definitivecoder]

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

[skhanal]

I have not worked with MySQL, but this kind of query is possible in Oracle using "Connect By" clause.

In SQL Server, you need to use cursor and temp table to push/pop current record and find the child records.

This is from SQL Server books online. You can write something like this

---
CREATE PROCEDURE expand (@current char(20)) as
SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (item char(20), level int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1

WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
BEGIN
SELECT @current = item
FROM #stack
WHERE level = @level
SELECT @line = space(@level - 1) + @current
PRINT @line
DELETE FROM #stack
WHERE level = @level
AND item = @current
INSERT #stack
SELECT child, @level + 1
FROM hierarchy
WHERE parent = @current
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END -- WHILE

[greenman]

Your simple request is possible, as I understand it. Given a simplified data set:

SELECT * FROM parents;
+------+--------------+-------------+
| P_ID | P_NAME       | P_PARENT_ID |
+------+--------------+-------------+
|    1 | me           |           2 |
|    2 | dad          |           3 |
|    3 | granddad     |        NULL |
|    4 | moi          |        NULL |
|    5 | kiddie1      |           4 |
|    6 | kiddie2      |           4 |
|    7 | grandkiddie1 |           5 |
+------+--------------+-------------+

You can return all of the kids and grandkids for 'moi' with the following:

SELECT p2.P_NAME as kids 
FROM parents AS p1, parents AS p2 
WHERE p1.P_ID = p2.P_PARENT_ID 
AND (p1.P_ID=4 OR p1.P_PARENT_ID=4);
+--------------+
| kids         |
+--------------+
| kiddie1      |
| kiddie2      |
| grandkiddie1 |
+--------------+

[jgeewax]

Getting the children + grandchildren works for those two levels because you are storing P_ID and P_PARENT_ID (two levels: current + one above) If you need three levels then you can store the third level (ie, P_GPARENT_ID).

I don't recommend this as there are cases where you want the "(n*great) grandparent" of a row (ie, n=1: great grandparent, n=2: great great grandparent) and it becomes inefficient to store the nth great grandparent as a separate column.

The alteratives (storing hierarchical data) have been discussed in many places, and I have put some of those together with a bit of commentary.

http://jgeewax.wordpress.com/2006/07...onal-database/