hierarchical retreival of records in postgresql I am using postgresql-9.2 . I have a table of products which stores the information about the products and its child table is content table , which contains the products linked to it.

PRODUCT MASTER TABLE

MASTER_ID                NAME 

1                      PRODUCT1
2                      PRODUCT2
3                      PRODUCT3
4                      PRODUCT4
5                      PRODUCT5
6                      PRODUCT6
7                      PRODUCT7
8                      PRODUCT8
9                      PRODUCT9
10                     PRODUCT10


PRODUCT_CONTENT TABLE

PRODUCT_CONTENT_ID                     PRODUCT_MASTER_ID        CONTENT_ID
A1                                          1                       2
A2                                          1                       3
A3                                          1                       4
A4                                          2                       3
A5                                          2                       4
A6                                          2                       5
A7                                          3                       5
A8                                          3                       7
A9                                          3                       8
A10                                         5                       8
A11                                         7                       9
A12                                         8                       10


I am writing a query to retrieve all the products(ids associated with them) with their content products which should display something like this hierarchy

MAINPRODUCT                     SUBPRODUCT
    2                               3
    2                               4
    2                               5
    3                               5
    3                               7
    3                               8
    5                               8   
    7                               9
    8                               10


I have written the following query

 SELECT PM1.PRODUCT_MASTER_ID AS MAIN,PC.CONTENT_ID AS CONTENT
    FROM PRODUCT_CONTENT PC 
    LEFT JOIN PRODUCT_MASTER PM ON PM.PRODUCT_MASTER_ID = PC.CONTENT_ID
    LEFT JOIN PRODUCT_MASTER PM1 ON 
    PM1.PRODUCT_MASTER_ID = PC.PRODUCT_MASTER_ID
    WHERE PC.PRODUCT_MASTER_ID IN 
      (
       SELECT PC.CONTENT_ID FROM PRODUCT_CONTENT PC 
       LEFT JOIN PRODUCT_MASTER PM ON 
          PM.PRODUCT_MASTER_ID = PC.CONTENT_ID
       LEFT JOIN PRODUCT_MASTER PM1 ON 
          PM1.PRODUCT_MASTER_ID = PC.PRODUCT_MASTER_ID
       WHERE PC.PRODUCT_MASTER_ID IN ('1')
      )


IT RETURNS SOMETHING LIKE THIS

MAIN            CONTENT

2                       3
2                       4
2                       5
3                       5
3                       7
3                       8
This is a simple adjacency list traversal problem. You're not even trying to build paths, just find nodes from a root node or set. That's pretty much a textbook example of what a recursive CTE is for.

http://sqlfiddle.com/#!12/8c406/12

WITH RECURSIVE children(product_content_id, product_master_id, content_id) AS (
  SELECT pc.product_content_id, pc.product_master_id, pc.content_id
  FROM product_content pc
  WHERE pc.product_master_id = '1'
  UNION
  SELECT pc.product_content_id, pc.product_master_id, pc.content_id
  FROM product_content pc
  INNER JOIN children c ON (pc.product_master_id = c.content_id)
)
SELECT
  product_master_id AS mainproduct,
  content_id AS subproduct
FROM children;


You could pretty much adapt this from the PostgreSQL manual, which includes an example on edgelist graph traversal.



Database design side-note: Please don't use character fields for numbers. It's also not usually a good idea to use keys like A10 if the A and 10 parts have any kind of separate meaning at all.