CODEWITHSUNDEEP
sql
TheFrost
TheFrost

Reputation: 1265

Select record, and if it has children, select the newest child instead

I have a table:

element_id, element_parent_id

Records are:

1, null
2, 1
3, 1
4, null

So, visualization might look like:

1
  2
  3
4

The question is, how to select for:

form_id
3
4

...in other words: how to select parent if there is no children, or the newest child if those children exist. So far I managed to select for:

1 and 4
2 and 3
1, 2, 3 and 4

Upvotes: 1

Views: 56

Answers (1)

LSerni
LSerni

Reputation: 57418

Just to be a bit more useful I'll explain my reasoning to arrive at the final query (at the bottom).

You are selecting two different entities from the same table, so you need a JOIN of the table against itself; this will give you parents and children.

But children may not be there and so it will have to be a LEFT JOIN.

 SELECT p.id, c.id AS cid
 FROM yourtable AS p 
 LEFT JOIN yourtable AS c ON (c.parent_id = p.id);

+------+------+
| id   | cid  |
+------+------+
|    1 |    3 | # This one...
|    1 |    2 | # ...and this one must be grouped, and 3 taken.
|    3 | NULL | <-- this must be ignored because it's a child
|    4 | NULL |
|    2 | NULL | <-- this must be ignored because it's a child
+------+------+

Now to refine, we see that we need to ignore children in the "parent" role and to this purpose we add WHERE p.parent_id IS NULL. "Real" children have a parent, and will then be skipped.

Note: this is the point where we would need to do something more complicated if we had a multi-level hierarchy. If we only wanted the bottom level, i.e. the "true" children (ignore those parents that have themselves a parent), we could for example run a second LEFT JOIN to get the *grand*parents, and impose that the grandparent's id be not NULL. This is only true for third level and greater grandchildren. Or we could get the children of the children and impose that they have NULL id, i.e. they don't exist; this is only true for the bottom or last-level children. Other requirements could call for yet another set of bounds.

Then you want the "top" child in relation to a parent id, and this calls for a GROUP BY, which will yield the desired result when children are there, or NULL when there aren't. 

 SELECT p.id, MAX(c.id) AS cid
     FROM yourtable AS p 
     LEFT JOIN yourtable AS c ON (c.parent_id = p.id)
     WHERE p.parent_id IS NULL
     GROUP BY p.id;

+------+------+
| id   | cid  |
+------+------+
|    1 |    3 |
|    4 | NULL |
+------+------+

In that case we see the information is in the parent-side column (id).

And to choose which, I would resort to a SUBSELECT.

SELECT CASE WHEN cid IS NULL THEN id ELSE cid END AS wanted
FROM ( 

    SELECT p.id, MAX(c.id) AS cid 
    FROM yourtable AS p
    LEFT JOIN yourtable AS c ON (c.parent_id = p.id)
    WHERE p.parent_id IS NULL
    GROUP BY p.id 
) AS x;

Upvotes: 2

Related Questions