Recursive SQL select

Question

I have items with categories and those categories can also have categories how can I find the first parent. For example...

CategoriesTbl
CategoryId | CategoryName   | parent
-------------------------------------
1          | Category1      | 0
2          | Category2      | 1
3          | Category3      | 2

ItemsTbl
ItemId     | CategoryId
-------------------------------------
1          | 3

How could I do a select on the item and join it to the category table with the parent category (CategoryId = 1). The parent category could be nested infinitely deep.

Answer 1

MySQL does not support recursive SQL queries. But there are other ways to store such parent-child data that allow you to get whole trees using a single SQL query.

See also:

• My answer to What is the most efficient/elegant way to parse a flat table into a tree?

• My presentation, "Models for Hierarchical Data with SQL and PHP"

• My book, "SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming"

Answer 2

You might be able to use something like this as a starting point.

For Example,

Table

Rank chid parid

7 6

5 4

4 3

3 2

2 Null

My output should be

Rank chiid parid

1 5 4

2 4 3

3 3 2

4 2 Null

CODE SNIPPET:

declare @table table(child int, parid int)
insert into @table
select 7, 6 union all
select 5, 4 union all
select 4, 3 union all
select 3, 2 union all
select 2, null

;with list
( tLevel,
  child,
  parId
) as
( select
  1 as tLevel,
  child,
  parId
  from @table a
  where parId is null
  union all
  select
  tLevel + 1,
  b.child,
  b.parId
  from list a
  join @table b
   on b.parId = a.child
)
select
  rank() over(order by tLevel desc)
    as Rank,
  child,
  parId
from list



/* -------- Sample Output: --------
Rank                 child       parId
-------------------- ----------- -----------
1                    5           4
2                    4           3
3                    3           2
4                    2           NULL
*/