Getting lowest hierarchy child in join select using closure table

Question

I have a closure table HIERARCHY

ancestor | descendant | depth
100      | 100        | 0
2        | 100        | 1
3        | 100        | 2
4        | 100        | 3

and a joined table PROPERTIES

id       | key        | value
4        | action     | DEFAULT
4        | duration   | PT1H
100      | action     | OVERRIDE
100      | duration   | PT1M

I can get the whole sub-tree using the query

  SELECT id, key, value
    FROM hierarchy, properties
   WHERE id = ancestor
     AND descendant = 100
ORDER BY depth

What would be the simplest/fastest way to get the lowest hierarchy member?, ie. having min(depth)

100      | action     | OVERRIDE
100      | duration   | PT1M

I still need to keep the hierarchy, meaning that if 100 isn't found in the query, 4 would show up.

In other words, I'm trying to find all the rows of the tree member with the lowest depth possible, possibly without duplicating the query in the WHERE clause

The database is the currently full-released mysql, ie. 5.7

Leo.W · Answer 1 · 2017-07-14T11:02:46.497

http://sqlfiddle.com/#!6/ce317/16

With A as(
    Select 
      id, _key, value, depth
    From 
      hierarchy
    Join
      properties
    On id = ancestor And descendant = 100
    ) 
Select id, _key, value from A
Where depth = (select min(depth) from A)

For MySQL, use a temp table...

CREATE TEMPORARY TABLE A AS 
(
    Select 
      id, _key, value, depth
    From 
      hierarchy
    Join
      properties
    On id = ancestor And descendant = 100
); 
Select id, _key, value from A
Where depth = (select min(depth) from A);

Or, hope that MySQL will cache ... http://sqlfiddle.com/#!9/d2350e/1

Select id, _key, value
From
(
    Select id, _key, value, depth From hierarchy Join properties On id = ancestor And descendant = 100
) B
Join
    (
      Select min(A.depth) min
      From
      (
        Select id, _key, value, depth From hierarchy Join properties On id = ancestor And descendant = 100
      ) A
    )C
On
  B.depth = C.min

About cache. https://dba.stackexchange.com/questions/44266/does-mysql-cache-queries

This would be great, but so far mysql still doesn't support common table expressions. I'll update the question so it's more clear. — fridaynosaur, Jul 13 '17 at 17:21
Thought so - I'm looking for a simpler way than using the same query twice (ie. join or a subquery) and temporary table would a pain to use as it's not just a query. Guess I'll have to wait for mysql 8. — fridaynosaur, Jul 14 '17 at 17:40

Getting lowest hierarchy child in join select using closure table

1 Answers1