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?

Edit the question and complete the minimal reproducible example, i.e. provide INSERT statements for sample data (paste the text, don't use images, don't link to external sites) (dito) and the desired result with that sample data in tabular text format. — sticky bit
– sticky bit, Commented Jan 4, 2022 at 18:53
Hey, @stickybit I actually thought that my question is quite complete. Should I add the data from the 3 tables in the question? Can you give me some more feedback and tell me how to improve the question? — KlauRau
– KlauRau, Commented Jan 4, 2022 at 19:03
Yes, as I wrote, add some sample data (a meaning handful is enough) for the tables and the actual result you want with that data. Just think like this: There's someone who is willing to work on your problem and provide you a solution if possible. But that someone want's to have something to work with, i.e. the relevant part of your environment to work on the solution and test it. For that they need sample data in a consumable way (hence it should be INSERT statements) and the desired result to check, if the solution is right and to better understand what you want. — sticky bit
– sticky bit, Commented Jan 4, 2022 at 19:09
I see, that's good! But having the CREATE statements in there was helpful too, you shouldn't have removed them. And as I wrote, if you really want to make it easy for someone who is willing to help you, there should be INSERT statements for the data. — sticky bit
– sticky bit, Commented Jan 4, 2022 at 19:46

Edouard · Accepted Answer · 2022-01-04 20:08:40Z

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.

Collectives™ on Stack Overflow

How to join multiple tables when creating a CTE Recursive Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related