By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table pcrelations(
id INT NOT NULL AUTO_INCREMENT,
pid char(1) NOT NULL,
cid char(1) NOT NULL,
value varchar(10),
PRIMARY KEY ( id )
);
insert into pcrelations values(0, 'A', 'B', 'AB');
insert into pcrelations values(0, 'B', 'C', 'BC');
insert into pcrelations values(0, 'C', 'A', 'CA1');
insert into pcrelations values(0, 'C', 'A', 'CA2');
insert into pcrelations values(0, 'C', 'D', 'CD');
SELECT * FROM pcrelations;
| id | pid | cid | value |
|---|---|---|---|
| 1 | A | B | AB |
| 2 | B | C | BC |
| 3 | C | A | CA1 |
| 4 | C | A | CA2 |
| 5 | C | D | CD |
SELECT t1.id leg1, t2.id leg2, t3.id leg3
, t1.pid p1id, t2.pid p2id, t3.pid p3id
, t1.value val1, t2.value val2, t3.value val3
FROM pcrelations t1
JOIN pcrelations t2 ON t2.pid = t1.cid
JOIN pcrelations t3 ON t3.pid = t2.cid
WHERE t3.cid = t1.pid
AND t1.pid < t2.pid
AND t1.pid < t3.pid;
| leg1 | leg2 | leg3 | p1id | p2id | p3id | val1 | val2 | val3 |
|---|---|---|---|---|---|---|---|---|
| 1 | 2 | 3 | A | B | C | AB | BC | CA1 |
| 1 | 2 | 4 | A | B | C | AB | BC | CA2 |