4

I have a database table which looks like this:

id | parent_id | status
1  | null      | 1
2  | null      | 0
3  | null      | 1
4  | 2         | 1
5  | 4         | 1

So, as you can see, we have some categories being children of each other, and the depth is unlimited.

What I want to know, is there an easy way to find out if any parents in a particular tree has status 0?

I know if there was a fixed depth then I could just do that many joins, and then use Min(status) to find out if any of them are set to 0, but I don't know how to do this having an undefined depth?

Hailwood
  • 89,623
  • 107
  • 270
  • 423
  • How on earch parent_id can be `null` :D – Himanshu Oct 06 '12 at 04:20
  • 2
    @hims056 - I'd imagine parent_id would be null if it didn't have a parent... so it'd be the chicken that laid the first egg – Mike Oct 06 '12 at 04:22
  • what do you mean that a parent has status 0? Plz give an example – Ankur Oct 06 '12 at 04:24
  • @Mike is correct, if a category does not have a parent (hence it is a root category) then it has a parent_id of `null` Think of the categories like a muli-level site navigation. – Hailwood Oct 06 '12 at 04:25
  • @Ankur So, for example, if I was using the `category 5` as a base, I would want to know if either `category 4`, or `category 2` has status `0` – Hailwood Oct 06 '12 at 04:32
  • i hope this can help you [http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree](http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree) see this PPT [http://www.slideshare.net/billkarwin/models-for-hierarchical-data](http://www.slideshare.net/billkarwin/models-for-hierarchical-data) – M.I.T. Oct 06 '12 at 04:49
  • i have the same problem this kinda then i have generated one closure table and it is a best way to solve this issue – M.I.T. Oct 06 '12 at 04:51

1 Answers1

0

With the adjacency list model that you are using it isn't possible because you would need to walk the graph upwards recursively, which is something that MySQL can't do. You should consider switching your data to the nested sets model where you can easily get the path to the root.

AndreKR
  • 32,613
  • 18
  • 106
  • 168
  • Yeah, I have had quite a [play with nested sets](http://stackoverflow.com/questions/6299905/dealing-with-nested-sets-in-mysql) but due to the fact that I will often be inserting nodes into the middle of a tree they aren't really practical. – Hailwood Oct 06 '12 at 04:29
  • "something that SQL can't do" should be "something that MySQL can't do", SQL can do WITH RECURSIVE just fine. – mu is too short Oct 06 '12 at 04:34
  • Oh, I wasn't aware of that... still stuck in '92. ;) Edited. – AndreKR Oct 06 '12 at 04:40