Retrieving a set of all Nth children

Question

The situation:

• MySQL 5.0
• 2 tables
• 1-to-many parent-child foreign key relationship (A.ID = B.PARENT_ID)

What's needed:

• A SELECT query that yields the set of all the Nth children (sorted by their IDs)
• Also need a form for use in UPDATE

Example:

Table B

| ID | PARENT_ID |
------------------
|  1 |     1     |
|  2 |     1     |
|  3 |     1     |
|  4 |     2     |
|  5 |     2     |
|  6 |     2     |
|  7 |     2     |
|  8 |     3     |
|  9 |     3     |
| 10 |     4     |

Desired Result Set for, e.g., all 2nd children

| A.ID | B.ID | B.PARENT_ID |
-----------------------------
|    1 |    2 |           1 |
|    2 |    5 |           2 |
|    3 |    9 |           3 |

Perhaps something to do with a feature of GROUP BY that I am not seeing?

My mind has totally become stuck seeing the solution to this problem procedurally. All help much appreciated.

Answer 1

MySQL does not have a mechanism to do this Nth relation. Oracle has an extension and maybe others.

What is the context?

If you are building a HTML form for update, generate the data with for loop and put the ID's in the table for simple update upon submit.

Answer 2

Don is correct - MySQL doesn't have ranking functionality. But luckily, it does allow you to initialize & reference a variable so you can code the logic for ranking.

This will return the rows for the 2nd children:

SELECT x.aid,
       x.bid,
       x.parent_id
  FROM (SELECT a.id 'aid',
               b.id 'bid',
               b.parent_id,
               CASE WHEN b.parent_id = @parent_id THEN @rownum := @rownum +1 ELSE @rownum := 1 END AS rownum,
               @parent_id := b.parent_id
          FROM TABLE_A a
          JOIN TABLE_B b ON b.parent_id = a.id
          JOIN (SELECT @rownum := 0, @parent_id := NULL) r
      ORDER BY b.parent_id, b.id) x
 WHERE x.rownum = 2

Change the WHERE x.rownum = ? to whatever Nth level children you want. The ORDER BY in the subquery is important to make sure the ranking comes out properly.

It's not clear to me how you want to use this for an UPDATE query - provide more detail & I'll update to suit your needs.