CODEWITHSUNDEEP
phpmysqldatabaserecursionnested
Ali
Ali

Reputation: 267059

Nested/Recursive MySQL query

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

It can go down 3 levels deep, like this:

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?

Upvotes: 1

Views: 887

Answers (2)

Sal
Sal

Reputation: 1655

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'

Upvotes: 1

Kyle Macey
Kyle Macey

Reputation: 8154

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'
  )
)

Upvotes: 1

Related Questions