By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table pcrelations (
id int not null auto_increment primary key,
pid varchar(4) not null,
cid varchar(4) not null,
value varchar(10)
);
insert into pcrelations
(pid, cid, value) values
('A', 'B', 'AB')
, ('B', 'C', 'BC')
, ('C', 'A', 'CA1')
, ('C', 'A', 'CA2')
, ('C', 'D', 'CD')
Records: 5 Duplicates: 0 Warnings: 0
select
t1.id as leg1, t2.id as leg2, t3.id as leg3
-- , t1.cid as cid1, t2.cid as cid2, t3.cid as cid3
, t1.pid as pid1, t2.pid as pid2, t3.pid as pid3
, t1.value as val1, t2.value as val2, t3.value as val3
from pcrelations t1
inner join pcrelations t2
on t2.pid = t1.cid
and t2.value > t1.value
inner join pcrelations t3
on t3.pid = t2.cid
and t3.cid = t1.pid
and t3.value > t2.value;
| leg1 | leg2 | leg3 | pid1 | pid2 | pid3 | val1 | val2 | val3 |
|---|---|---|---|---|---|---|---|---|
| 1 | 2 | 3 | A | B | C | AB | BC | CA1 |
| 1 | 2 | 4 | A | B | C | AB | BC | CA2 |