0

I have adapted this from here.

SELECT T2.id, T2.name
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := category_id_parent FROM category WHERE id = _id) AS category_id_parent,
        @l := @l + 1 AS lvl
    FROM
         (SELECT @r := (SELECT c.id FROM category c WHERE c.name="Category D"), @l := 0) vars,
        category h
    WHERE @r <> -1) T1
JOIN category T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC

How would I adapt this further to accept a list of ids instead of just one?

So instead of SELECT c.id FROM category c WHERE c.name="Category D"

It would be SELECT bc.category_id FROM businesscategory bc WHERE bc.business_id=55 (which returns a list of ids as opposed to just the one)

Example of current query:

I have a table category

id    |    name    | category_id_parent
-1         N/A              -1
1       Category A          -1
2       Category B          -1
3       Category C           1
4       Category D           3

This query would return

id    |   name
1       Category A
3       Category C
4       Category D

As Category D's parent is C whose parent is A.

RedHorse
  • 77
  • 5
  • 1
    You've posted a fairly complex query, which I can't understand at first sight (and I stare at SQL 10-12 hours per day). Your question should first begin by _explaining_ what this query does. Then, you may talk about what new features you need. – Tim Biegeleisen Feb 22 '19 at 10:04
  • @TimBiegeleisen added example of what the query does – RedHorse Feb 22 '19 at 10:12

0 Answers0