Connect By Prior Equivalent for MySQL

by

MySQL doesn’t support recursive queries so you have to do it the hard way:

  1. Select the rows where ParentID = X where X is your root.
  2. Collect the Id values from (1).
  3. Repeat (1) for each Id from (2).
  4. Keep recursing by hand until you find all the leaf nodes.

If you know a maximum depth then you can join your table to itself (using LEFT OUTER JOINs) out to the maximum possible depth and then clean up the NULLs.

You could also change your tree representation to nested sets.

More Related Contents:

  1. MySQL create stored procedure syntax with delimiter
  2. mysql dynamic query in stored procedure
  3. MySQL : transaction within a stored procedure
  4. MySQL Stored Procedures not working with SELECT (basic question)
  5. Split a string and loop through values in MySQL stored procedure
  6. My SQL Dynamic query execute and get ouput into a variable in stored procedure
  7. What is dynamic SQL?
  8. Recreating a query using INNER JOIN? [duplicate]
  9. Create a Cumulative Sum Column in MySQL
  10. Equivalent of explode() to work with strings in MySQL
  11. MySQL Trigger after update only if row has changed
  12. MySQL “WITH” clause
  13. mysql select dynamic row values as column names, another column as value
  14. MySQL “Group By” and “Order By”
  15. MySQL DELETE FROM with subquery as condition
  16. MySQL Query to select data from last week?
  17. Rename a column in MySQL
  18. Find most frequent value in SQL column
  19. How to convert number of week into date?
  20. update multiple rows using limit in mysql?
  21. Mysql 1050 Error “Table already exists” when in fact, it does not
  22. Looping Over Result Sets in MySQL
  23. How to get the latest record in each group using GROUP BY? [duplicate]
  24. How do I UPDATE a row in a table or INSERT it if it doesn’t exist?
  25. SQL unique varchar case sensitivity question
  26. Is it possible to have a default parameter for a mysql stored procedure?
  27. How do I count columns of a table
  28. Why MySQL is giving error “Not allowed to return a result set from a function”?
  29. MySQL 5.6 – DENSE_RANK like functionality without Order By
  30. How do I write an SP in phpMyAdmin (MySQL)?

Leave a Comment