CODEWITHSUNDEEP
sql
user1178394
user1178394

Reputation: 83

Simple Parent/Child query

I need to return a names of categories/sub-categories, and the recursion will only go 1 level deep (never more than 1 sub category)

Table Data would be something along the lines of:

CATEGORY NAME - CAT ID  - PARENT ID
Cat A         -    1    -    0
Cat B         -    2    -    0
Sub Cat A     -    3    -    1

DESIRED RESULTS
Cat A
Cat A > Sub Cat A
Cat B

Thanks!

EDIT: (here's what I tried so far)

SELECT FC1.CATEGORY_ID,
       FC1.CATEGORY_NAME,
       FC1.PARENT_CATEGORY_ID,
       FC2.PARENT_CATEGORY_ID,
       FC2.CATEGORY_NAME AS PARENT_CATEGORY_NAME
  FROM CATEGORY FC1
  LEFT
  JOIN CATEGORY FC2
    ON FC2.CATEGORY_ID = FC1.CATEGORY_ID
 ORDER BY CATEGORY_NAME

Upvotes: 0

Views: 164

Answers (1)

ruakh
ruakh

Reputation: 183251

You need to change this:

    ON FC2.CATEGORY_ID = FC1.CATEGORY_ID

(joining each record to itself) to this:

    ON FC2.CATEGORY_ID = FC1.PARENT_CATEGORY_ID

(joining each record to its parent).

Upvotes: 3

Related Questions