0

I now sit for the first time in a MySQL database and need to create a "tree traversal" query.

I have 2 tables:

Team:

--------------------------------
|INT id|VARCHAR name|INT parent|
--------------------------------
 1      Test 1       null
 2      Test 2       1
 3      Test 3       2
 4      Test 4       1
 5      Test 5       null

Crew:

--------------------------------
|INT id|VARCHAR name|INT teamId|
--------------------------------

In this database, each team can have a child team, and each team can have a number of crew associated.

What I'm trying to create is a query, where I can enter a Team.id and it will return all Crew that is a member of a team, where Team.id is an ancestor of their team (not just parent, general ancestor). But also the crew of the Team.id itself.

So for example if I set Team.id (in the query) to 2, it would return the crew of "Test 2" and "Test 3". If I entered 1 it would return the crew of "Test 1", "Test 2", "Test 3" and "Test 4".

I have searched here on StackOverflow and came up with some answers, but they were way to generalized for my experience with MySQL.

Thank you in advance.

FirstHorizon
  • 118
  • 1
  • 11
  • https://stackoverflow.com/questions/5291054/generating-depth-based-tree-from-hierarchical-data-in-mysql-no-ctes/5291159#5291159 – Adrian Stanculescu Sep 02 '15 at 13:36
  • Thank you for the link. Unfortunately I'm not profecient enough in MySQL to expand that solution to include lookup in two tables. MySQL is not the database I'm usually working with. – FirstHorizon Sep 02 '15 at 13:46

1 Answers1

0

To my knowledge, recursive select is not implemented in MySql. It might be possible by writing a recursive MySql function.

LavaSlider
  • 2,494
  • 18
  • 29