Nested/Recursive MySQL query

Question

I have a db table called foo. It has these fields:

id
name
parentId

It can go down 3 levels deep, like this:

Foo 1 (parentId : 0)
- Foo 2 (parentId: 1) (foo's child 1)
  - Foo 3 (parentId: 2) (both foo1 and foo2's child)

Now the problem is, in my users table, there's a field called fooId. I want to get all the users that belong to a top level foo.

E.g, if a user's fooId is 3, and I want to get all users related to fooId 1 (from the above example), then that user should be counted within the results.

If it was just two levels deep, I could do something like this:

`SELECT stuff FROM users, foo WHERE foo.parentId = '1' AND user.fooId = foo.id

But how can I do it in the present situation?

the following might prove helpful stackoverflow.com/questions/5291054/hierarchical-sql-problem/… — Jon Black
– Jon Black, Commented Jan 22, 2012 at 22:38

Kyle Macey · Accepted Answer · 2012-01-22 22:09:58Z

1

I'm sure my logic is screwed up at some point here... but it may get you in the right direction

SELECT stuff 
FROM users, foo
WHERE (foo.parentId = '1' AND user.fooId = foo.id) 
OR user.fooId IN (
  SELECT fooid from foo where parentID IN (
    SELECT fooid from foo where parentID = '1'
  )
)

answered Jan 22, 2012 at 22:09

Kyle Macey

8,1543 gold badges40 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ilion Over a year ago

At a glance this should work but I don't think it would be highly scalable, so it's going to depend on how many possible IDs it has to deal with. A Nested Set Model would probably be much more efficient, but they can take some maintenance work. Proper triggers can usually take care of the problems with them.

Kyle Macey Over a year ago

I've got to agree that this doesn't work out great performance-wise. It seems, though like this should run through a relationship table of sorts. Like, a self-referencing habtm association

Sal · Accepted Answer · 2012-01-22 22:56:07Z

1

May be something like this...

SELECT stuff FROM users, foo WHERE foo.parentId = '1' AND user.fooId = foo.id UNION SELECT stuff FROM users WHERE fooId = '3'

EDIT: SELECT stuff FROM users, foo WHERE foo.parentId = '1' AND user.fooId = foo.id UNION ALL SELECT stuff FROM users WHERE fooId = '3'

edited Jan 22, 2012 at 22:56

answered Jan 22, 2012 at 22:16

Sal

1,64512 silver badges9 bronze badges

2 Comments

Kyle Macey Over a year ago

This still doesn't account for other Foo's. This is static to what was given as the example by OP. Again, if Foo2 has another child Foo4, or even Foo5, this query will only get Foo3. Or if Foo1 is given child Foo6, then the tree breaks down again.

Sal Over a year ago

It will get really messy in mysql to select whole tree, it's better to use PHP to complete the task :/

Collectives™ on Stack Overflow

Nested/Recursive MySQL query

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related