1

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?

Ali
  • 261,656
  • 265
  • 575
  • 769
  • the following might prove helpful http://stackoverflow.com/questions/5291054/hierarchical-sql-problem/5291159#5291159 – Jon Black Jan 22 '12 at 22:38

2 Answers2

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'
  )
)
Kyle Macey
  • 8,074
  • 2
  • 38
  • 78
  • 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](http://www.fliquidstudios.com/2008/12/23/nested-set-in-mysql/) would probably be much more efficient, but they can take some maintenance work. Proper triggers can usually take care of the problems with them. – Ilion Jan 22 '12 at 22:36
  • 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 – Kyle Macey Jan 22 '12 at 22:40
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'

Sal
  • 1,657
  • 12
  • 9
  • 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. – Kyle Macey Jan 23 '12 at 00:06
  • It will get really messy in mysql to select whole tree, it's better to use PHP to complete the task :/ – Sal Jan 23 '12 at 00:54