recursive select and join related table

Question

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)

score 2 · Accepted Answer · answered Sep 05 '16 at 06:36

2

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
;

answered Sep 05 '16 at 06:36

Thanks so much I get it!! – user1775888 Sep 05 '16 at 08:56
Should I add `p.product_category_id IN (SELECT * FROM pc) ` at last? cause I use your query seems select all product list – user1775888 Sep 05 '16 at 09:46

Suraiya Khan · Answer 2 · 2016-09-05T06:35:55.183

1

Note:I do not have postgresql installed on my system so I am talking based on concepts. I do not see where you have defined the foreign key constraint ... still I am assuming you have done so. And I have not checked the correctness of the CTE/Common Table Expression (- basically with recursive portion of your SQL). Assuming that CTE is correct -

How is about replacing

SELECT * FROM product_category WHERE id IN (SELECT * FROM pc)

with

SELECT * FROM product WHERE product_category_id IN (SELECT * FROM pc)

The correctness of the CTE is the next thing I am going to check.

So apparently the following should work:

;
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 pc.id = <Your product category id of interest>
    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 id as product_id FROM product WHERE product_category_id 
IN (
   SELECT * FROM pc
);

edited Sep 05 '16 at 06:35

answered Sep 05 '16 at 05:33

Suraiya Khan

102
4

WHERE id = $1 ... in that part please replace the id with pc.id – Suraiya Khan Sep 05 '16 at 06:05
Hey i did not get your last/above question. Also I did not try to optimize your query. Let me re-post (but this time your whole query with my minimal edit/correction). And then let me know if you still have questions. Thanks. – Suraiya Khan Sep 05 '16 at 06:30
Putting a `;` before and after the statement is useless. You only need a `;` at the end. – Sep 05 '16 at 06:36
Hi a_horse_with_no_name : based on the state of how you are testing the query in postgresql lack of first ; may cause problem ... basically it happens with Common Table Expression for the situations when for some reason previously printed statement in psql (true for mssql as well) did not end in semicolon. Why not try it to check it yourself? Thanks. :) – Suraiya Khan Sep 05 '16 at 06:41
This has nothing to do with a CTE. **Every** statement needs to be terminated with a `;` in SQL (or psql) – Sep 05 '16 at 06:42
http://stackoverflow.com/questions/6938060/common-table-expression-why-semicolon I guess I have this habit from my recent work experience in MSSQL :) Please do not use it if you can do without it. – Suraiya Khan Sep 05 '16 at 06:48
Sorry, @a_horse_with_no_name, to pick nits: SQL statements are not *terminated*, but *separated* by semicolons, and `psql` takes them as a sign that the query is complete. You can run `psql -c "SELECT 42"` just fine without a semicolon. – Laurenz Albe Sep 05 '16 at 07:47
@LaurenzAlbe: Nitpicking as well: the [Postgres manual](https://www.postgresql.org/docs/current/static/app-psql.html#R2-APP-PSQL-4) calls it _terminated_: "*when a command-terminating semicolon is reached*" – Sep 05 '16 at 07:57

recursive select and join related table

2 Answers2