How to join multiple tables when creating a CTE Recursive Query

Question

I am trying to create a recursive query that will take attributes from 3 different tables but I fail to JOIN the tables correctly and therefore I cannot select the desired attributes:

I have 3 tables:

tb_register

  CREATE TABLE olympic.tb_register (
  athlete_id    CHARACTER(7) NOT NULL,
  round_number  INT NOT NULL,
  discipline_id INT NOT NULL,
  register_position INT DEFAULT NULL,
  CONSTRAINT pk_register PRIMARY KEY (athlete_id, round_number, discipline_id),
  CONSTRAINT fk_register_athlete FOREIGN KEY (athlete_id) REFERENCES olympic.tb_athlete (athlete_id),
  CONSTRAINT fk_register_round FOREIGN KEY (discipline_id, round_number) REFERENCES olympic.tb_round (discipline_id, round_number)
);


athlete_id round_number discipline_id register_position

"1349038"   3   16  0
"1393212"   3   16  1
"1318621"   3   16  2
"1451030"   3   16  3
"1343607"   3   16  4
"1435826"   3   16  5


INSERT INTO olympic.tb_register(athlete_id, round_number, discipline_id, register_position) VALUES('1349038', 3, 16, 0);
INSERT INTO olympic.tb_register(athlete_id, round_number, discipline_id, register_position) VALUES('1393212', 3, 16, 1);
INSERT INTO olympic.tb_register(athlete_id, round_number, discipline_id, register_position) VALUES('1318621', 3, 16, 2);
INSERT INTO olympic.tb_register(athlete_id, round_number, discipline_id, register_position) VALUES('1451030', 3, 16, 3);
INSERT INTO olympic.tb_register(athlete_id, round_number, discipline_id, register_position) VALUES('1343607', 3, 16, 4);
INSERT INTO olympic.tb_register(athlete_id, round_number, discipline_id, register_position) VALUES('1435826', 3, 16, 5);

tb_athlete

CREATE TABLE olympic.tb_athlete    (
  athlete_id    CHARACTER(7) NOT NULL,
  name          CHARACTER VARYING(50) NOT NULL,
  country       CHARACTER VARYING(3) NOT NULL,
  substitute_id  CHARACTER(7) DEFAULT NULL,
  CONSTRAINT pk_athlete PRIMARY KEY (athlete_id),
  CONSTRAINT fk_athlete_substitute FOREIGN KEY (substitute_id) REFERENCES olympic.tb_athlete (athlete_id)
);

athlete_id    athlete_name              country
        
"1349038"   "AALERUD Katrine"            "NOR"  
"1393212"   "ABASS Abobakr"              "SUD"  
"1451030"   "ABDALLA Abubaker Haydar"    "QAT"  
"1444255"   "ABDEL LATIF Radwa"          "EGY"

INSERT INTO olympic.tb_athlete(athlete_id, name, country, substitute_id) VALUES('1346266','AALERUD Katrine','NOR',NULL);
INSERT INTO olympic.tb_athlete(athlete_id, name, country, substitute_id) VALUES('1344792','ABASS Abobakr','SUD',NULL);
INSERT INTO olympic.tb_athlete(athlete_id, name, country, substitute_id) VALUES('1328854','ABDALLA Abubaker Haydar','QAT',NULL);
INSERT INTO olympic.tb_athlete(athlete_id, name, country, substitute_id) VALUES('1306332','ABDEL LATIF Radwa','EGY',NULL);

tb_discipline

CREATE TABLE olympic.tb_discipline  (
  discipline_id  INT NOT NULL,
  name           CHARACTER VARYING(50) NOT NULL,
  inventor       CHARACTER VARYING(50) NOT NULL,
  type           CHARACTER VARYING(10) NOT NULL,
  object_type    CHARACTER VARYING(20) DEFAULT NULL, 
  CONSTRAINT ck_discipline_type CHECK (type IN ('RUN', 'JUMP', 'THROW')),
  CONSTRAINT pk_discipline PRIMARY KEY (discipline_id)
);

discipline_id  discipline_name inventor type
    
    16  "Triathlon" "Carina Melina" "RUN"   

INSERT INTO olympic.tb_discipline(discipline_id, name, inventor, type, object_type) VALUES(16,'Triathlon','Carina Melina','RUN',null);

Desired output

I am trying to create a recursive query to obtain this result:

discipline_name, round_number, register_position, a_position 
Triathlon,           3,              0,           0:AALERUD Katrine,
Triathlon,           3,              1,           0:AALERUD Katrine -> 1: ABASS Abobakr
Triathlon,           3,              2,           0:AALERUD Katrine -> 1: ABASS Abobakr -> ABDALLA Abubaker Haydar 

where a_position is a list of athlete names with their respective position.

My code:

WITH RECURSIVE athlete_hierarchy AS (
  SELECT 
    discipline_name,
    round_number,
    register_position,
    CAST (athlete_name AS TEXT) AS a_position,
    register_position AS first_athlete
  FROM 
    olympic.tb_register
  JOIN tb_discipline
    ON discipline_id = tb_discipline.discipline_id
  JOIN tb_athlete
    ON athlete_id = tb_athlete.athlete_id;
  WHERE 
    round_number IS 3
  UNION ALL
  SELECT 
    a.discipline_name,
    a.round_number,
    a.register_position,
    CAST ( a.athlethe_name  || ' -> ' || a2.a_position AS TEXT) AS a_position,
    c2.first_athlete AS  first_athlete 
  FROM 
     olympic.tb_register  INNER JOIN athlete_hierarchy 
       ON (a.athlete_id = a2.athlete_id)
)
SELECT  
  discipline_name,
  round_number,
  register_position,
  a_position,
  first_athlete
FROM athlete_hierarchy;

Can anyone help me understand how to join 3 tables when creating a CTE Recursive Query?

Answer 1

Here is the solution :

WITH RECURSIVE athlete_hierarchy (discipline_id, discipline_name, round_number, register_position, a_position) AS
(SELECT 
    d.discipline_id,
    d.name,
    r.round_number,
    r.register_position,
    r.register_position || ':' || CAST (a.name AS TEXT)
  FROM tb_register AS r
  JOIN tb_discipline AS d
    ON r.discipline_id = d.discipline_id
  JOIN tb_athlete AS a
    ON r.athlete_id = a.athlete_id
  WHERE r.round_number = 3
    AND r.register_position = 0
  UNION ALL
  SELECT
    ah.discipline_id,
    ah.discipline_name,
    ah.round_number,
    r.register_position,
    ah.a_position || ' -> ' || r.register_position || ':' || CAST (a.name AS TEXT)
  FROM tb_register AS r
 INNER JOIN athlete_hierarchy AS ah
    ON ah.discipline_id = r.discipline_id
   AND ah.round_number = r.round_number
   AND ah.register_position = r.register_position - 1
 INNER JOIN tb_athlete AS a
    ON r.athlete_id = a.athlete_id
)
SELECT discipline_name, round_number, register_position, a_position
FROM athlete_hierarchy
ORDER BY register_position

and the test result from your data sample is in db<>fiddle.