Join with missing relationship

Question

I have 3 different type of objects ECR, ECN and MCN and I have a relation table where these are connected. Ideally the flow is ECR will be connected to ECN and then same ECN will be connected to MCN. Sometimes there are chances that ECR will directly connect to MCN and sometimes ECR will not be there. But the MCN is the last point and it will always have value. Please help to get the below output.

I have create a fiddle with sample data here

I have tried different join but I'm not able to get the required output

Try 1:

select relb.pid,relb.sid,rela.sid 
from relationship rela 
left join relationship relb on rela.pid=relb.sid

Try 2:

select relb.pid,relb.sid,relc.sid
from relationship rela 
left join relationship relb on rela.pid=relb.sid
left join relationship relc on relc.pid=relb.sid

Answer 1

Maybe not the prettiest, but most easy to read and simple way to do is just to split your 3 relations into 3 different queries and make a union of them:

select ecr.pid AS ECR, ecn.pid AS ECN, ecn.sid AS MCN
from relationship ecr
INNER JOIN relationship ecn ON ecr.sid = ecn.pid 
where ecr.pid lIKE 'ECR%' AND ecr.sid lIKE 'ECN%'

UNION ALL

select ecr.pid AS ECR, NULL AS ECN, ecr.sid AS MCN
from relationship ecr
where ecr.pid lIKE 'ECR%' AND ecr.sid lIKE 'MCN%'

UNION ALL

select NULL AS ECR, ecn.pid AS ECN, ecn.sid AS MCN
from relationship ecn
where ecn.pid lIKE 'ECN%' AND ecn.sid lIKE 'MCN%'
AND NOT EXISTS (SELECT * FROM relationship ecr WHERE ecr.pid LIKE 'ECR%' AND ecr.sid = ecn.pid)