Getting all parent rows in one SQL query
Posted
by Alex
on Stack Overflow
See other posts from Stack Overflow
or by Alex
Published on 2010-03-14T10:24:46Z
Indexed on
2010/03/14
10:35 UTC
Read the original article
Hit count: 151
I have a simple MySQL table thats contains a list of categories, level is determined by parent_id:
id name parent_id
---------------------------
1 Home 0
2 About 1
3 Contact 1
4 Legal 2
5 Privacy 4
6 Products 1
7 Support 1
I'm attempting to make a breadcrumb trail. So i have the 'id' of the child, I want to get all available parents (iterating up the chain until we reach 0 "Home"). There could be any number or child rows going to an unlimited depth.
Currently I am using an SQL call for each parent, this is messy. Is there a way in SQL to do this all on one query?
© Stack Overflow or respective owner