CODEWITHSUNDEEP
sqldatabasepostgresql
Keith
Keith

Reputation: 959

SQL Categories - Trouble getting all parent_id names

I'm trying to get and show all the parent_id names based on the product_id. What I am doing is creating an admin page where I can add categories to specific products, but first I need to get each name in the categories table associated with parent_id before I can add / edit them.

So when I query product_id 99, I should get back: skateboarding -> action_sports -> sports

 Categories Table
 ----------------------------------
 | id |     name      | parent_id |
 ----------------------------------
 | 1  |    sports     |    null   |
 ----------------------------------
 | 2  | action_sports |     1     |
 ----------------------------------
 | 3  | skateboarding |     2     |
 ----------------------------------


 Product Category Joiner Table
 ----------------------------
 | product_id | category_id |
 ----------------------------
 |     99     |      3      |
 ----------------------------

The only thing I can think of is to join the tables. I was hoping to be able to join the same table twice on different id's, but that clearly doesn't work.

 SELECT categories.name, categories.parent_id FROM products 
 INNER JOIN product_category ON products.id = product_category.product_id
 INNER JOIN categories ON categories.id = product_category.category_id
 WHERE products.id = 99

 returns:

 -----------------------------
 |      name     | parent_id |
 -----------------------------
 | skateboarding |     2     |  <-- need to get name of each parent_id above
 -----------------------------

Any ideas? Hopefully I've explained ok enough for it to make a little bit of sense

Upvotes: 0

Views: 79

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

You can use a recursive CTE, but first on the categories but only on the ones that are needed:

with recursive cte as (
      select c.id, c.name, c.parent_id
      from products p join
           categories c
           on c.id = p.category_id
      where p.product_id = 99
      union all
      select c.id, c.name, p.parent_id
      from cte join
           products p
           on cte.parent = c.id
     )
select *
from cte;

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

If you don't know how many levels there are to get to parent_id null, you can use a recursive cte to solve this.

with recursive cte as
  (select p.id as product_id,
          c.name,
          c.parent_id
   from products p
   join product_category pc on p.id = pc.product_id
   join categories c on c.id = pc.category_id
   union all
   select p.id,
          c.name,
          c.parent_id
   from cte r
   join products p on p.id = r.product_id
   join categories c on c.id = r.parent_id
  )
select *
from cte
where product_id=99 --change this to the desired product_id

Upvotes: 0

Related Questions