0

I have these tables

person table with following data

person_id    description

1          first in the family
2          SON OF PERSON 1
3          SON OF PEROSN 2
4          SON OF PEROSN 3
5          SON OF PERSON 4
6          SON OF  PERSON 2
7          SON OF  PERSON 3
8          SON OF PERSON 3
9          SON OF PERSON 7

person_name table with following data

person_id  first_name

1        Santiago
2         Lautaro
3          Lucas
4          Franco
5          Agustín
6          MOLE
7          BULLY
8          CIVAL
9          NUMA

father table with following data

person_father_id  description
1              father of Lautaro
2              father of Lucas
3              father of Franco
4              father of Agustín
7              FATHER OF NUMA

children table with following data

 person_child_id   person_father_id
  2                 1
  3                 2
  4                 3
  5                 4
  6                 2
  7                 3
  8                 3
  9                 7

HOW TO GET RELATIONS BETWEEN FAMILY FOR EXAMPLE THE UNCLE OF PERSON NUMBER 4. AND THE NEPHEW OF PERSON NUMBER 2 AND THE GRAND FATHER OF PERSON 4

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
JASA
  • 5
  • 2
  • 1
    This sounds like homework. Have you tried a query yet? – Tim Biegeleisen Sep 19 '18 at 16:57
  • It also sounds quite similar to your previous questions. Please edit your question to show what you have tried to do to solve this task, and what was wrong with it (errors, wrong results), and explain how you decide and identify what your starting point is (which person number) and the relation(s) you are looking for. – Alex Poole Sep 19 '18 at 17:35
  • @AlexPoole you are right, the same tables but I need relations . – JASA Sep 19 '18 at 19:39
  • and person number means PERSON ID – JASA Sep 19 '18 at 19:40

1 Answers1

1

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TYPE number_table AS TABLE OF NUMBER;

CREATE TABLE relationships (child_id, father_id ) AS
SELECT 2, 1 FROM DUAL UNION ALL
SELECT 3, 2 FROM DUAL UNION ALL
SELECT 4, 3 FROM DUAL UNION ALL
SELECT 5, 4 FROM DUAL UNION ALL
SELECT 6, 2 FROM DUAL UNION ALL
SELECT 7, 3 FROM DUAL UNION ALL
SELECT 8, 3 FROM DUAL UNION ALL
SELECT 9, 7 FROM DUAL;

Query 1:

SELECT n.COLUMN_VALUE ||' IS THE NEPHEW OF ' || u.COLUMN_VALUE AS nephew
FROM   (
         SELECT children AS nephews,
                PRIOR CHILDREN MULTISET EXCEPT NUMBER_TABLE( father_id ) AS uncles
         FROM   (
           SELECT father_id,
                  CAST( COLLECT( child_id ORDER BY child_id ) AS number_table ) AS children
           FROM   relationships
           GROUP BY father_id
         )
         WHERE LEVEL = 2
         CONNECT BY father_id MEMBER OF PRIOR children
       ) t
       CROSS JOIN TABLE( t.nephews ) n
       CROSS JOIN TABLE( t.uncles ) u

Results:

|               NEPHEW |
|----------------------|
| 4 IS THE NEPHEW OF 6 |
| 7 IS THE NEPHEW OF 6 |
| 8 IS THE NEPHEW OF 6 |
| 5 IS THE NEPHEW OF 7 |
| 5 IS THE NEPHEW OF 8 |
| 9 IS THE NEPHEW OF 4 |
| 9 IS THE NEPHEW OF 8 |

Query 2:

SELECT PRIOR father_id || ' IS THE GRANDFATHER OF ' || child_id AS grandfather
FROM   relationships
WHERE  LEVEL = 2
CONNECT BY father_id = PRIOR child_id

Results:

|               GRANDFATHER |
|---------------------------|
| 1 IS THE GRANDFATHER OF 3 |
| 1 IS THE GRANDFATHER OF 6 |
| 2 IS THE GRANDFATHER OF 4 |
| 2 IS THE GRANDFATHER OF 8 |
| 2 IS THE GRANDFATHER OF 7 |
| 3 IS THE GRANDFATHER OF 5 |
| 3 IS THE GRANDFATHER OF 9 |
MT0
  • 143,790
  • 11
  • 59
  • 117