Reverse Mysql Query

Question

I have two databases that look like this:

| idElement | idClient | idSubClient | idSport | idCategory | idTeam | idParent |
|-----------|----------|-------------|---------|------------|--------|----------|
|         1 |        1 |          -1 |  (null) |     (null) | (null) |   (null) |
|         2 |        1 |          -1 |      30 |     (null) | (null) |        1 |
|         3 |        1 |          -1 |  (null) |        100 | (null) |        2 |
|         4 |        1 |          -1 |      31 |     (null) | (null) |        1 |
|         5 |        1 |          -1 |  (null) |     (null) |    150 |        3 |
|         6 |        1 |          -1 |  (null) |     (null) |    151 |        3 |
|         7 |        1 |          -1 |  (null) |        101 | (null) |        4 |
|         8 |        1 |          -1 |  (null) |        101 | (null) |        2 |
|         9 |        1 |          -1 |  (null) |     (null) |    152 |        7 |
|        10 |        1 |          -1 |  (null) |     (null) |    153 |        7 |

| idRef | idElement | idUser |
|-------|-----------|--------|
|     1 |         1 |   2000 |
|     2 |         1 |   2001 |
|     3 |         1 |   2002 |
|     4 |         1 |   2003 |
|     5 |         2 |   2004 |
|     6 |         2 |   2005 |
|     7 |         3 |   2001 |
|     8 |         4 |   2003 |
|     9 |         9 |   2004 |

I want with idUser, idClient, idSubclient create reverse recursive query that returns if parent of element is sport.

For example:

In my case User with id 2004 is assigned to 2 element that is sport but also is assigned to element 9. The element with id 9 is a team that hangs on category with id 7 in this case it is not a sport then I need to consult his parent to check if is sport...

What I expect

| idElement | |-----------| | 2 | | 4 |

SQLFiddle Example

Answer 1

In MariaDB 10.4 you can use a Recursive CTE to find the rows you want. For example:

with recursive
u as (
  select
    u.idUser, u.idElement as root, 
    o.idElement, o.idParent, o.idSport, 1 as lvl from organigram_users u
  join organigram o on o.idElement = u.idElement
  where u.idUser = 2004
 UNION ALL
  select u.idUser, u.root, o.idElement, o.idParent, o.idSport, u.lvl + 1
  from u 
  join organigram o on o.idElement = u.idParent and u.idSport is null
),
s as (
  select *
  from (
    select *,
      row_number() over(partition by idUser, root order by lvl desc) as rn
    from u 
  ) x
  where rn = 1
)
select * from s; -- idElement shows 2, 4

Result:

idUser  root  idElement  idParent  idSport  lvl  rn
------  ----  ---------  --------  -------  ---  --
  2004     2          2         1       30    1   1
  2004     9          4         1       31    3   1

The column idElement shows the utmost parent that has a non-null idSport value: one is at first level, the other one is at third level.

See running example at db<>fiddle.

Answer 2

This is my approach to solve what I have understood. To check if the parent of an user is sport or not:

select if(o.idSport>0,'TRUE','FALSE') as isSport,o.idElement, o.idParent,u.idUser from organigram o join organigram_users u on (u.idElement = o.idElement) where u.idUser = 2004; Please comment in case of query or further discussion.