0

Note: I got similar problems but without where condition.

I want to get all user ids who are reporting to a particular user id.(Directly reporting and indirect reporting)

Egs:

If I input user_id = 1, I want to get user_ids 2,3,4,5. ie

2 is reporting to 1,

3 is reporting to 2,

4 is reporting to 3,

5 is reporting to 4.

enter image description here

Here I am adding the SQL Fiddle

Note: Prefering a solution without stored procedure or functions

Thanks.

Sanjay Kumar N S
  • 4,653
  • 4
  • 23
  • 38
  • Have a look at recursive select query for MySQL - for example this link: http://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql – Jesper Bangsholt Nov 26 '15 at 13:24
  • You need some form of recursion to achieve something like this. Recursive CTEs are not available in MySQL. So it seems the only option is to use a stored procedure. Have a look at [this](http://dba.stackexchange.com/questions/7147/find-highest-level-of-a-hierarchical-field-with-vs-without-ctes/7161#7161) post in dba.stackexchange. – Giorgos Betsos Nov 26 '15 at 13:26
  • @Jesper Bangsholt there are some limitations for the solutions which you suggested. Here there can be a chance of re parenting the users which cause the solution failed. – Sanjay Kumar N S Nov 27 '15 at 04:51
  • @Giorgos Betsos, Stored procedure is an option, but trying to find out a solution without that. Sorry I forget to specify in the question states that "Without stored procudeure". – Sanjay Kumar N S Nov 27 '15 at 04:53

1 Answers1

1

For these proposes is good to use material paths (list all ancestors) approach building hierarchical structure instead of pairs of id->parentId. So your table and query may look like this http://sqlfiddle.com/#!2/a202d/2

If you are not limited for MySQL database Postgres have a great data type and functions for material paths approach http://www.postgresql.org/docs/9.4/static/ltree.html

max
  • 2,757
  • 22
  • 19
  • Also, there is a great question/overview about hierarchical structures in DBs http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database – max Nov 26 '15 at 13:55
  • Yours solution works. This logic I had taken into consideration but not by adding an extra column, but dynamically creating that extra column and filter with that. Can I get something like this? Thanks – Sanjay Kumar N S Nov 27 '15 at 04:55
  • @SanjayKumarNS, anyway, building the column requires recursive tree traverse wich is unavailable without CTE or stored procedures. Also, dynamic generation is very expensive in terms of performance. If you would like to keep your data clean you may build a separate table which will store info about hierarchy instead of maintaining the column in the main table. – max Nov 27 '15 at 20:16