2

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

The Impaler
  • 45,731
  • 9
  • 39
  • 76
cplaiuu
  • 163
  • 4
  • 18
  • You have 2 TABLES that look like that – RiggsFolly May 07 '20 at 12:44
  • Your SQLFiddle is based on MySQL 5.6. This question is easy to solve using MySQL 8.x. Can you verify your MySQL version? – The Impaler May 07 '20 at 13:45
  • @TheImpaler I have: `mysql Ver 15.1 Distrib 10.4.6-MariaDB, for Linux (x86_64) using readline 5.1` – cplaiuu May 07 '20 at 13:47
  • @TheImpaler is possible to do what I want with MariaDB 10.4 ? – cplaiuu May 07 '20 at 14:13
  • Yes, it can be done. One question, though: why do you need to try both paths (2-Yes, 9-No -> 7-No -> 4-Yes). Do you need to check BOTH paths to verify all parents are "sport"? – The Impaler May 07 '20 at 15:06
  • @TheImpaler fist I do query that retrun all elements of user 2004 select idElemento from organigram_users where idUser = 2004 and after I want to know if he are in both sports. – cplaiuu May 07 '20 at 15:18
  • @cplaiuu Does my answer solve your question, or did you need something different? – The Impaler May 07 '20 at 15:43

2 Answers2

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.

The Impaler
  • 45,731
  • 9
  • 39
  • 76
0

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.

jsgrewal12
  • 128
  • 9