0

I'm using this query to get parents of a customer but it returns no result:

SELECT *
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM data_customers_map_parents WHERE customer_id = _id) AS _pid,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 1, @l := 0) vars,
        data_customers_map_parents m
    WHERE @r != null) T1
JOIN data_customers_map_parents T2
ON T1._id = T2.customer_id
ORDER BY T1.lvl DESC;

What's wrong with this query?

halfer
  • 19,824
  • 17
  • 99
  • 186
Manish Jangir
  • 5,329
  • 4
  • 42
  • 75
  • 3
    MySQL doesn't support recursive queries. If you need to do this, I would suggest a stored procedure or an alternative data structure (that stores the path to each customer). – Gordon Linoff Aug 02 '17 at 11:44
  • Well could you please suggest me a way to do that – Manish Jangir Aug 02 '17 at 11:46
  • Could you please add sample data here or you can check following link as well. https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query/33737203#33737203 – Fahad Anjum Aug 02 '17 at 12:47

0 Answers0