SQL combine foreign key values of all parents into child

Question

In an informix database, I have a hierarchical tree structure, where a child entry can have any level of parents (parent, grandparent, etc). via relation to its parent entry.

Every entry has a collection of attribute names and values.

The tables are modeled is as follows:

node:

+-------------+----------------------+--------+
|     id      |       parn_id        |  name  |
+-------------+----------------------+--------+
| int         | int                  | string |
| primary key | existing id, or null |        |
+-------------+----------------------+--------+

vals:


+-----------------------+-------------+---------+
|          id           |   atr_id    | atr_val |
+-----------------------+-------------+---------+
| int                   | int         | string  |
| foreign key from node | primary key |         |
+-----------------------+-------------+---------+

look:

+-----------------------+--------+
|        atr_id         |  name  |
+-----------------------+--------+
| int                   | string |
| foreign key from vals |        |
+-----------------------+--------+

I need a sql query which returns all of the parent's (vals, look) pairs when asking for a child.

For example, if I have

Parent: (valP, nameP), (valP2, nameP2)
*
* * * Child (valC, nameC)
      *
      * * * GrandChild (valGC, nameGC)

And I query for the GrandChild, I want it to return:

GrandChild (valP, nameP), (valP2, nameP2), (valC, nameC), (valGC, nameGC)

Answer 1

Using a recent Informix version ( I am using Informix 14.10.FC1 ) you can use the CONNECT BY clause to work with hierarchical queries.

The setup, based on your description:

CREATE TABLE node
(
    id      INTEGER PRIMARY KEY,
    parn_id INTEGER,
    name    CHAR( 20 ) NOT NULL
);
INSERT INTO node VALUES ( 1, NULL, 'Node_A' );
INSERT INTO node VALUES ( 2, NULL, 'Node_B' );
INSERT INTO node VALUES ( 3, NULL, 'Node_C' );
INSERT INTO node VALUES ( 4, 2, 'Node_D' );
INSERT INTO node VALUES ( 5, 3, 'Node_E' );
INSERT INTO node VALUES ( 6, 3, 'Node_F' );
INSERT INTO node VALUES ( 7, 4, 'Node_G' );

CREATE TABLE vals
(
    id      INTEGER NOT NULL REFERENCES node( id ),
    atr_id  INTEGER PRIMARY KEY,
    atr_val CHAR( 20 ) NOT NULL    
);
INSERT INTO vals VALUES ( 1,  1, 'Value_A_1' );
INSERT INTO vals VALUES ( 2,  2, 'Value_B_1' );
INSERT INTO vals VALUES ( 2,  3, 'Value_B_2' );
INSERT INTO vals VALUES ( 3,  4, 'Value_C_1' );
INSERT INTO vals VALUES ( 3,  5, 'Value_C_2' );
INSERT INTO vals VALUES ( 4,  6, 'Value_D_1' );
INSERT INTO vals VALUES ( 5,  7, 'Value_E_1' );
INSERT INTO vals VALUES ( 5,  8, 'Value_E_2' );
INSERT INTO vals VALUES ( 6,  9, 'Value_F_1' );
INSERT INTO vals VALUES ( 7, 10, 'Value_G_1' );

CREATE TABLE look
(
    atr_id  INTEGER NOT NULL REFERENCES vals( atr_id ),
    name    CHAR( 20 ) NOT NULL
);
INSERT INTO look VALUES (  1, 'Look_A_1' );
INSERT INTO look VALUES (  2, 'Look_B_1' );
INSERT INTO look VALUES (  3, 'Look_B_2' );
INSERT INTO look VALUES (  4, 'Look_C_1' );
INSERT INTO look VALUES (  5, 'Look_C_2' );
INSERT INTO look VALUES (  6, 'Look_D_1' );
INSERT INTO look VALUES (  7, 'Look_E_1' );
INSERT INTO look VALUES (  8, 'Look_E_2' );
INSERT INTO look VALUES (  9, 'Look_F_1' );
INSERT INTO look VALUES ( 10, 'Look_G_1' );

we can use the CONNECT BY to find the child parents, for example:

-- Starting from 'Node_G'
SELECT
    n.id,
    n.parn_id,
    n.name,
    CONNECT_BY_ROOT n.name AS starting_node
FROM
    node AS n
START WITH n.name = 'Node_G'
CONNECT BY PRIOR n.parn_id = n.id
ORDER BY
    n.name
;

-- RESULTS:
     id     parn_id name        starting_node

      2             Node_B      Node_G
      4           2 Node_D      Node_G
      7           4 Node_G      Node_G

And then we can join with the attribute tables:

SELECT
    vt1.starting_node,
    v.atr_val,
    l.name
FROM
(
    SELECT
        n.id,
        n.parn_id,
        n.name,
        CONNECT_BY_ROOT n.name AS starting_node
    FROM
        node AS n
    START WITH n.name = 'Node_G'
    CONNECT BY PRIOR n.parn_id = n.id
) AS vt1
INNER JOIN vals AS v
ON
    v.id = vt1.id
INNER JOIN look AS l
ON
    l.atr_id = v.atr_id
ORDER BY
    vt1.starting_node, v.atr_val
;

-- RESULTS:
starting_node        atr_val              name

Node_G               Value_B_1            Look_B_1
Node_G               Value_B_2            Look_B_2
Node_G               Value_D_1            Look_D_1
Node_G               Value_G_1            Look_G_1

If we remove the START WITH clause, we get the hierarchical results for each node:

SELECT
    vt1.starting_node,
    v.atr_val,
    l.name
FROM
(
    SELECT
        n.id,
        n.parn_id,
        n.name,
        CONNECT_BY_ROOT n.name AS starting_node
    FROM
        node AS n
    CONNECT BY PRIOR n.parn_id = n.id
) AS vt1
INNER JOIN vals AS v
ON
    v.id = vt1.id
INNER JOIN look AS l
ON
    l.atr_id = v.atr_id
ORDER BY
    vt1.starting_node, v.atr_val
;

-- RESULTS:
starting_node        atr_val              name

Node_A               Value_A_1            Look_A_1
Node_B               Value_B_1            Look_B_1
Node_B               Value_B_2            Look_B_2
Node_C               Value_C_1            Look_C_1
Node_C               Value_C_2            Look_C_2
Node_D               Value_B_1            Look_B_1
Node_D               Value_B_2            Look_B_2
Node_D               Value_D_1            Look_D_1
Node_E               Value_C_1            Look_C_1
Node_E               Value_C_2            Look_C_2
Node_E               Value_E_1            Look_E_1
Node_E               Value_E_2            Look_E_2
Node_F               Value_C_1            Look_C_1
Node_F               Value_C_2            Look_C_2
Node_F               Value_F_1            Look_F_1
Node_G               Value_B_1            Look_B_1
Node_G               Value_B_2            Look_B_2
Node_G               Value_D_1            Look_D_1
Node_G               Value_G_1            Look_G_1