How to make a SQL query for an ordered list with sub-groups?

Question

Hi

I have a table with this structure

 id integer
  parent_id integer
  order_n integer
  info text

Each row could have a parent row (parent_id, null if doesn't have a parent), and an order of insertion (order_n). Every time a row is inserted, the order_n field will be set with the correlative "inside his parent". So, two rows of first level will be order_n = 1 and order_b = 2. But a new row "inside" row 1 will be order_n = 1

Example

  id    parent_id   order_n   info
  1     null        1         "Beatles"
  2     null        2         "Stones"
  3     1           1          "Paul"
  4     1           2          "John"
  5     2           1          "Mick"
  6     2           2          "Keith"

The sub-levels are infinite.

The thing I'm trying to do (and I fail miserably), is to make a query who retrieve all the rows for any level (including the first level), and order it according his order_n attribute, but grouping the nested rows. For example, in the previous example, we need to retrieve the results this way

1     null        1         "Beatles"
3     1           1          "Paul"
4     1           2          "John"
2     null        2         "Stones"
5     2           1          "Mick"
6     2           2          "Keith"

I'm trying and trying but I know very little about SQL, I will thanks in advance all your wise advice.

I'm using MySQL, but the ideal is try something "sql standard"

The inner levels are infinite.

Answer 1

This will be dynamic SQL and will be a bit complicated to get into.

We will need a bit more detail to determine how help with this but it will begin with the following:

You'll need to determine how many rows deep the query will be, otherwise you risk an infinite loop.

Determine which columns you want from each row

Basically you'll create a varchar in a stored procedure which will grow as needed for your query.

You'll want to set a variable to the highest n-order in this query, then loop through, adding columns, joins, orders, groups and (most importantly!) aliases for each nested table.

Here's some pseudo-code:

int maxval = 0
select maxval = max(n-level) from table where ...
string newalias = ""
string oldalias = newguid
int cnt = 0
int parentid = NULL

string selectstatement = "select "
string joinstatement = "from table as" oldalias
string orderstatement = "order by"

while cnt < maxval
    newalias = newguid

    selectstatement = selectstatement + "," + newalias + "." + columnnames
    joinstatement = joinstatement + " JOIN table as " + newalias + "on " + newalias + ".columnname = " + oldalias + ".columname"
    orderstatement = orderstatement + "," + newalias + ".columnname"
maxval = maxval + 1
oldalias= newalias
loop

Answer 2

It's not a hard query to write until your very last line "The inner levels are infinite.". You will be joining the table onto itself once for every level you need. If you had a predefined maximum of 5 levels, you could join the table to itself 5 times (left joins) to accomplish that.

very psuedo code:

Select whatever
from mytable my1
left join mytable my2 on my1.id = my2.parent_id
where whatever
order by case when parent_id is null then id else parent_id end, 
case when parent_id  is null then 0 else order_n end

The case statements in the order by clause are designed to identify the top parent record and group them with the rest.

Want more levels? Expanding the join statement: left join mytable my3 on my2.id = my3.parent_id left join mytable my4 on my3.id = my4.parent_id left join mytable my5 on my4.id = my5.parent_id

without a 'maximum' number of levles, dynamic SQL as per Matthew PK is (as far as I know) your only recourse.

my1,my2,my3, etc might not be the easiest alias naming convention either, pick something you can follow.