definitivecoder
asked on
MYSQL Equivenlant for Oracle 'LEVEL'
I understand that in Oracle there is a keyword "LEVEL". Given a pattern such as personId = parentId, LEVEL accepts as arguments a personId/parentId and the number of levels to go down.
For example, say my personId = 15 and my parentId = 10. Also, assume that I have a child whose personId = 20. Using the LEVEL function I pass in 10 and 2. This would return the following records:
where personId = 10
where parentId = 10
where parentId = 15
where parentId = (all my sibling's personIds)
(my parent, his/her children, and their children's children) Make sense?
Having said that, I was wondering if MYSQL has anything equivelant to Oracle's LEVEL. My problem is that given a personId, I want to retrieve that person's children and all their children's children.
ABRV. DB SCHEMA:
P_ID
P_FIRSTNAME
P_PARENT_ID
Thanks for any direction you can give me.
For example, say my personId = 15 and my parentId = 10. Also, assume that I have a child whose personId = 20. Using the LEVEL function I pass in 10 and 2. This would return the following records:
where personId = 10
where parentId = 10
where parentId = 15
where parentId = (all my sibling's personIds)
(my parent, his/her children, and their children's children) Make sense?
Having said that, I was wondering if MYSQL has anything equivelant to Oracle's LEVEL. My problem is that given a personId, I want to retrieve that person's children and all their children's children.
ABRV. DB SCHEMA:
P_ID
P_FIRSTNAME
P_PARENT_ID
Thanks for any direction you can give me.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
until MySql has stored procedures, that is :D
agree.
You can get children and grandchildren in one query by using inner joins, but you have to know how many levels down you want and construct the query manually.
You can get children and grandchildren in one query by using inner joins, but you have to know how many levels down you want and construct the query manually.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
very nice (if it works, but I trust you :D )
ping
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.