user1775888 user1775888 - 3 months ago 13
SQL Question

recursive select and join related table

CREATE TABLE IF NOT EXISTS "product_category"(
"id" SERIAL NOT NULL,
"parent_id" integer DEFAULT NULL,
"name" varchar DEFAULT NULL,
PRIMARY KEY ("id")
);


CREATE TABLE IF NOT EXISTS "product"(
"id" SERIAL NOT NULL,
"product_category_id" integer DEFAULT NULL,
PRIMARY KEY ("id")
);


I have two table like above,

the
product_category
is hierarchy,

and
product.product_category_id
fk
product_category.id
.

How to select all product under specific product_category id,

e.g

if input product_category 1,

output -> product:1, product:2

if input product_category 2

output -> product:2

product_category
id | parent_id | name
1 | | parent
2 | 1 | child
3 | 2 | child child


product
id | product_category_id
1 | 1
2 | 3


query

like this ?? but this return only product_category list .... I want product list

WITH RECURSIVE pc AS (
SELECT pc.id AS id
FROM product_category pc

LEFT JOIN product p ON p.product_category_id = pc.id

WHERE id = $1

UNION ALL

SELECT child.id
FROM product_category AS child

LEFT JOIN product p ON p.product_category_id = child.id

JOIN pc ON pc.id = child.parent_id
)
SELECT * FROM product_category WHERE id IN (SELECT * FROM pc)

Answer

You should first build up the list of categories, then join that to the products.

WITH RECURSIVE pc AS (
  SELECT id
  FROM product_category 
  WHERE id = $id

  UNION ALL

  SELECT child.id
    FROM product_category AS child
    JOIN pc ON pc.id = child.parent_id 
)
SELECT pr.*
FROM product pr
  JOIN pc on pr.product_category_id = pc.id
;