i have a table "table1" with an "parents_list" inside. Now i will get only one parent with the highest status for each row and from this parent the next parent (2 ups). The query i wrote is this one. But it returns for "parent_name & parent_name_2" only NULL:
SELECT t1.id, parents.name as parent_name, parents2.name as parent_name_2
from table1 as t1
LEFT JOIN table1 as parents ON parents.id = (
SELECT id from table1 WHERE parents_list IN (SUBSTR(REPLACE(t1.parents_list,';', ','),1 , CHAR_LENGTH(t1.parents_list) - 1)) AND status > 1 ORDER BY status DESC LIMIT 1
)
LEFT JOIN table1 as parents2 ON parents2.id = (
SELECT id from table1 WHERE parents_list IN (SUBSTR(REPLACE(parents2.parents_list,';', ','),2 , CHAR_LENGTH(parents2.parents_list) - 2)) AND status > 1 ORDER BY status DESC LIMIT 1
)
WHERE t1.date BETWEEN '2018-05-15' AND '2018-06-20'
The table:
| id | name | date | parents_list | status |
| ---: | --- | --- | --- | ---: |
| 1 | test 1 | 2018-05-18 | | 3 |
| 2 | test 2 | 2018-05-25 | ;1; | 3 |
| 3 | test 3 | 2018-05-23 | ;5;1;4; | 8 |
| 4 | test 4 | 2018-05-30 | ;1;3; | 5 |
| 5 | test 5 | 2018-06-05 | ;2;4;3; | 6 |
The result of row "ID: 5" should be:
| id | parent_name| parent_name_2 |
| 5 | test 3 | test 1 |
but the current result is:
| id | parent_name| parent_name_2 |
| 5 | NULL | NULL |
How could i bring this query to work or is there a better way to do this?
P.S. i can't change the table design :(
