1

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
  • 2
    Check out recursive queries: postgresql.org/docs/current/static/queries-with.html – a_horse_with_no_name Aug 25 '14 at 11:11
  • Actually I have to get all the rows in the Record later , to execute it one by one ,,, That's why I cant use Recursive queries... – Geek Aug 25 '14 at 11:16
  • Why not? I don't understand your argument. – Colin 't Hart Aug 25 '14 at 11:27
  • Ok. I Have to write a query to get the rows in a Record loop and process it one by one . Is that possible with recursive queries? – Geek Aug 25 '14 at 11:39
  • 1
    @Geek First, you rarely actually have to process things in a loop. Usually you land up able to do it with insert/update/delete using joins, or using a CTE. Second, even if you do have to loop, it makes more sense to fetch the rows once then iterate over the result. – Craig Ringer Aug 25 '14 at 11:48
2

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.

| improve this answer | |
  • Thanx for your time. I will remember the notes provided.One additional thing I have to ask you is that the resulting query contains all the records. Now I have to get main product id one by one to implement some business logic.How can I do that ? Plz help – Geek Aug 26 '14 at 13:45
  • @Geek That's not really clear enough to help you much. Just iterate over them client-side? Use a cursor? A temp table? Wrap the whole thing up as a subquery-in-FROM or another CTE term and then SELECT my_business_logic_function(record) FROM the_results? Hard to really say without a useful description of what you need. If you find yourself stuck, post a new detailed question on that problem and then post a link to it here. Include sample data, expected results, description of business logic, whether business logic is client-side/server side, etc etc tec. – Craig Ringer Aug 27 '14 at 6:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.