#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    4
    Rep Power
    0

    writing recursive sql


    Hi,

    Table structure

    CREATE TABLE table1
    (
    id ,
    requestlevel ,
    requestname ,
    requestlabel ,
    parentid


    CREATE TABLE table2
    (
    id ,
    version ,
    requesttypeid ,
    previousrequestid ,

    )

    table1 values

    id level parentid
    1 0 0
    2 0 0
    3 1 1
    4 1 1
    5 1 1
    6 2 4
    7 2 4
    8 2 3
    9 2 3
    10 3 8
    11 3 8
    12 3 6
    13 3 6
    14 4 10
    15 4 10
    17 5 14
    18 5 16
    16 4 13


    table 2 values

    1 1 15 18



    in table2 requesttypeid(max ID ( tableName: table1) is stored) and previousrequestypeid (prev max ID ( tableName: table1) is stored).

    requesttypeid all levels values -

    id parentid level

    15;10 ;4
    10;8;3
    8;3;2
    3;1;1
    1;0;0

    previousrequestypeid all levels values -

    id parentid level

    18;16;5
    16;13;4
    13;6;3
    6;4;2
    4;1;1
    1;0;0

    i have to show in this format -


    level5 : 18 --> null

    level 4 : 16 --> 15

    level 3 : 13 --> 10

    level 2 : 6 --> 8

    level 1 : 4 --> 3

    Can you help me in writing in recursive sql query to get in above format...
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    11
    "in table2 requesttypeid(max ID ( tableName: table1) is stored) and previousrequestypeid (prev max ID ( tableName: table1) is stored)."

    I do not understand what you mean here.


    Recursion in PgSQL is a matter of writing a CTE query (also known as a "with" query) that creates the correct link between the "current" level and the next/previous levels:
    http://www.postgresql.org/docs/8.4/static/queries-with.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    4
    Rep Power
    0
    table 2 values :

    id version requesttypeid previousrequestid
    1 1 15 18

    15 is id of table1
    18 is id of table1
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    11
    Ok, so you try something like:

    DROP TABLE IF EXISTS table1;
    DROP TABLE IF EXISTS table2;
    CREATE TABLE table1 (id INTEGER , title VARCHAR(255));
    CREATE TABLE table2 (table1_id_child INTEGER , table1_id_parent INTEGER);
    INSERT INTO table1 (id, title) VALUES (1,'Level 3'), (2, 'Level 5'),(3,'Level 4'),(4, 'Level 2'), (5,'Level 1'),(6,'Level A'),(7,'Level B');
    INSERT INTO table2 (table1_id_child, table1_id_parent) VALUES (5,null),(4,5),(1,4),(3,1),(2,3);
    INSERT INTO table2 (table1_id_child, table1_id_parent) VALUES (6,null),(7,6);

    WITH RECURSIVE treedata AS
    (
    SELECT table1_id_child, table1_id_parent FROM table2 WHERE table1_id_child=5
    UNION ALL
    SELECT p.table1_id_child, p.table1_id_parent
    FROM table2 AS p
    INNER JOIN treedata pr
    ON p.table1_id_parent = pr.table1_id_child
    )
    SELECT *
    FROM treedata INNER JOIN table1 ON table1.id = treedata.table1_id_child
    LIMIT 10;



    WITH RECURSIVE treedata AS
    (
    SELECT table1_id_child, table1_id_parent FROM table2 WHERE table1_id_child=6
    UNION ALL
    SELECT p.table1_id_child, p.table1_id_parent
    FROM table2 AS p
    INNER JOIN treedata pr
    ON p.table1_id_parent = pr.table1_id_child
    )
    SELECT *
    FROM treedata INNER JOIN table1 ON table1.id = treedata.table1_id_child
    LIMIT 10;
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    4
    Rep Power
    0
    Hi,
    Thanks for query.

    as written by you, i have to join those two queries and compare the levels and show it.

    How to join both the recursive queries ?
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    11
    The two queries are just two examples of the same query, that start a different node.


    You would have to implement *one* such a query and modify it to start at the node that you want to get the path from.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    4
    Rep Power
    0
    Hi,
    in one query, i need to pass two startnodes and get all the levels.

    How do i do that?

IMN logo majestic logo threadwatch logo seochat tools logo