Ok so following this question, I have realised that the JOIN I am attempting is unfortunately not so straight forward.
To amend the previous query, the foreign keys within the table create a hierarchy, and the reference is only populated if the entry is at the top level.
So what I need to achieve, is get the
S.name AS Origin,
M.area AS OriginArea
FROM cable C
INNER JOIN sub S ON C.sub = S.sub_id
INNER JOIN major M ON S.major_parent = M.major_id
Here is the fiddle for this query:
SELECT cable.`name`, sub.`name`, major.`name`, major.area FROM cable INNER JOIN sub ON cable.sub = sub.sub_id LEFT JOIN sub AS parent ON parent.sub_id = sub.sub_parent INNER JOIN major ON major.major_id = COALESCE (parent.major_parent,sub.major_parent) INNER JOIN area ON area.area_id = major.area
This way your hierarchy becomes something optional. If the entry in
sub has a parent the left join will find it, if it doesn't the left join will return
COALESCE (parent.major_parent, sub.major_parent) will just skip the
null and use the
sub.major_parent to join the
I think I messed up the join to the parent sub by using the
sub_parent = major_parent. It should be
sub_parent = sub_id shouldn't it?
LEFT JOIN sub AS parent ON parent.sub_id = sub.sub_parent
Also added it to the whole query on top.