Join table on self
I am trying to select all rows in a table where the parent id is the passed value, but I also want to select all rows whose parent id equals the above selected rows ID
Example structure
tbl_categories_id category_name category_parent
1 cat1 1
2 cat2 1
3 cat3 1
4 cat4 2
5 cat5 2
So select all rows where the category_parent=1, but of those there is one (tbl_categories_id=2) which has 2 child rows (4 & 5)
How would I write the sql?
http://sqlfiddle.com/#!2/097e0/2
Example structure
tbl_categories_id category_name category_parent
1 cat1 1
2 cat2 1
3 cat3 1
4 cat4 2
5 cat5 2
So select all rows where the category_parent=1, but of those there is one (tbl_categories_id=2) which has 2 child rows (4 & 5)
How would I write the sql?
http://sqlfiddle.com/#!2/097e0/2
Sharath S
So if you pass category_parent as 1, you want all its children and their children in a recursive way?
ASKER
Yep. So even though I am selecting where parent_id=1 my query should return
1 cat1 1
2 cat2 1
4 cat4 2
5 cat5 2
3 cat3 1
1 cat1 1
2 cat2 1
4 cat4 2
5 cat5 2
3 cat3 1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Will get back to this tomorrow, got into the middle of something else and don't want to get sidetracked.
Thanks Paul for posting the sqlfiddle from my post. will post the solution going forward.
:) no problem Sharath. Cheers, Paul.