Recursive MySQL Queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

Recursive MySQL Queries

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-25 : 07:49:16

Domonic Griffiths writes "Good afternoon

I'm trying to generate a recursive sql query in mysql which allows to generate the recordset whilst maintaining the id/parentID relationship and depth. The query i currently use is:

SELECT M2.ID, M2.parentID, M2.questionID
FROM tbl_forum AS M1
JOIN tbl_forum AS M2
ON M1.ID = M2.parentID
WHERE M2.questionID=150

as stated in the 4guysfromrolla site. However the traversal only seems to generate a flat recordset.

what i need is a tree like structure

parentID
  ---childID
    ---childID
    ---childID
      ---childID
        ---childID
    ---childID
      ---childID
  ---childID
    ---childID

can this be done using a recursive sql statement?"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-25 : 07:51:25

You can try this:

http://www.sqlteam.com/item.asp?ItemID=8866

Realize that it was written in T-SQL (SQL Team is a SQL Server site) so you'll have to translate any syntax differences for MySQL. Also look at Joe Celko's articles on nested sets, they might work better for you. Either method should be usable in MySQL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources