Fetching linked list in MySQL database

by

Some brands of database (e.g. Oracle, Microsoft SQL Server) support extra SQL syntax to run “recursive queries” but MySQL does not support any such solution.

The problem you are describing is the same as representing a tree structure in a SQL database. You just have a long, skinny tree.

There are several solutions for storing and fetching this kind of data structure from an RDBMS. See some of the following questions:

Since you mention that you’d like to limit the “depth” returned by the query, you can achieve this while querying the list this way:

SELECT * FROM mytable t1
 LEFT JOIN mytable t2 ON (t1.next_id = t2.id)
 LEFT JOIN mytable t3 ON (t2.next_id = t3.id)
 LEFT JOIN mytable t4 ON (t3.next_id = t4.id)
 LEFT JOIN mytable t5 ON (t4.next_id = t5.id)
 LEFT JOIN mytable t6 ON (t5.next_id = t6.id)
 LEFT JOIN mytable t7 ON (t6.next_id = t7.id)
 LEFT JOIN mytable t8 ON (t7.next_id = t8.id)
 LEFT JOIN mytable t9 ON (t8.next_id = t9.id)
 LEFT JOIN mytable t10 ON (t9.next_id = t10.id);

It’ll perform like molasses, and the result will come back all on one row (per linked list), but you’ll get the result.

More Related Contents:

  1. Is it possible to query a tree structure table in MySQL in a single query, to any depth?
  2. How to create a MySQL hierarchical recursive query?
  3. Generating Depth based tree from Hierarchical Data in MySQL (no CTEs)
  4. Hierarchical queries in MySQL
  5. Getting all parent rows in one SQL query
  6. Implementing a hierarchical data structure in a database
  7. MySQL Recursive get all child from parent
  8. MySQL Closure Table hierarchical database – How to pull information out in the correct order
  9. multiple query same table but in different columns mysql
  10. How can I make SQL case sensitive string comparison on MySQL?
  11. Import data in MySQL from a CSV file using LOAD DATA INFILE
  12. How to do a batch insert in MySQL
  13. Get current AUTO_INCREMENT value for any table
  14. INSERT INTO … SELECT FROM … ON DUPLICATE KEY UPDATE
  15. mysql trigger stored trigger is already used by statement which invoked stored trigger
  16. MySQL error 1241: Operand should contain 1 column(s)
  17. MySQL – Supertype/Subtype design
  18. Mysql: Order by like?
  19. Can’t import database through phpmyadmin file size too large
  20. How to change MySQL column definition?
  21. How can I undo a mysql statement that I just executed?
  22. Continue SQL query even on errors in MySQL workbench
  23. How to generate a UUIDv4 in MySQL?
  24. MySQL : how to remove double or more spaces from a string?
  25. is php sort better than mysql “order by”?
  26. Mysql returning clause equivalent
  27. How can “set timestamp” be a slow query?
  28. connecting to a docker-compose mysql container denies access but docker running same image does not
  29. MySQL’s lower_case_table_names won’t change
  30. #1214 – The used table type doesn’t support FULLTEXT indexes

Leave a Comment