How to find all IDs of children recursively?

by

There are two basic methods for doing this: adjacency lists and nested lists. Take a look at Managing Hierarchical Data in MySQL.

What you have is an adjacency list. No there isn’t a way of recursively grabbing all descendants with a single SQL statement. If possible, just grab them all and map them all in code.

Nested sets can do what you want but I tend to avoid it because the cost of inserting a record is high and it’s error-prone.

More Related Contents:

  1. MySQL query finding values in a comma separated string
  2. In MySQL queries, why use join instead of where?
  3. SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)
  4. Can table columns with a Foreign Key be NULL?
  5. MySQL not using indexes with WHERE IN clause?
  6. MySQL dump by query
  7. single fixed table with multiple columns vs flexible abstract tables
  8. How many rows in a database are TOO MANY?
  9. SQLite – ORDER BY RAND()
  10. SQL – How to transpose?
  11. Which SQL query is better, MATCH AGAINST or LIKE?
  12. MySQL equivalent of DECODE function in Oracle
  13. How to delete Duplicates in MySQL table
  14. Best database field type for a URL
  15. Query that ignore the spaces
  16. How to Optimize Queries in a Database – The Basics
  17. MySQL – how to front pad zip code with “0”?
  18. Create date from day, month, year fields in MySQL
  19. Find rows that have the same value on a column in MySQL
  20. what is the difference between GROUP BY and ORDER BY in sql
  21. How do I list all the columns in a table?
  22. Two single-column indexes vs one two-column index in MySQL?
  23. VARCHAR as foreign key/primary key in database good or bad?
  24. Alter a MySQL column to be AUTO_INCREMENT
  25. MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query
  26. How to delete from select in MySQL?
  27. Know relationships between all the tables of database in SQL Server
  28. MySQL – Replace Character in Columns
  29. sql pulling a row for next or previous row of a current row
  30. MySQL: Computed Column

Leave a Comment