Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>What you want is a common table expression. Unfortunately it looks like mysql doesn't support them. </p> <p>Instead you will probably need to use a loop to keep selecting deeper trees.</p> <p>I'll try whip up an example. To clarify, you're looking to be able to call the procedure with an input of say '1' and get back all the sub categories and subsub categories (and so on) with 1 as an eventual root? like</p> <pre><code>id parent 1 null 2 1 3 1 4 2 </code></pre> <p>?</p> <p>Edited:</p> <p>This is what I came up with, it seems to work. Unfortunately I don't have mysql, so I had to use sql server. I tried to check everythign to make sure it will work with mysql but there may still be issues.</p> <pre><code>declare @input int set @input = 1 --not needed, but informative declare @depth int set @depth = 0 --for breaking out of the loop declare @break int set @break = 0 --my table '[recursive]' is pretty simple, the results table matches it declare @results table ( id int, parent int, depth int ) --Seed the results table with the root node insert into @results select id, parent, @depth from [recursive] where ID = @input --Loop through, adding notes as we go set @break = 1 while (@break &gt; 0) begin set @depth=@depth+1 --Increase the depth counter each loop --This checks to see how many rows we are about to add to the table. --If we don't add any rows, we can stop looping select @break = count(id) from [recursive] where parent in ( select id from @results ) and id not in --Don't add rows that are already in the results ( select id from @results ) --Here we add the rows to the results table insert into @results select id, parent, @depth from [recursive] where parent in ( select id from @results ) and id not in --Don't add rows that are already in the results ( select id from @results ) end --Select the results and return select * from @results </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POHandling tree in a MySQL procedure
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USdavid
    1. USdavid
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COYes, that seems about right, unfortunately I don't know what tree depth can I expect. It can be three, it can be three hundread...
      singulars
      1. PO
      1. UScypher
    2. COEdited with a loop that might work
      singulars
      1. PO
      1. USdavid
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload