CODEWITHSUNDEEP
mysqljoin
Ben
Ben

Reputation: 2523

SQL Query to JOIN based on Heirarchical data

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 area.name for the cable (using this fiddle) entry, but I don't need the rest of the hierarchy in my selection.

Obviously, my previous attempt missed the fact that the major_parent field was sometimes empty.

This is my original query:

SELECT
  C.name,
  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
WHERE
  # Parameters
;

Upvotes: 2

Views: 126

Answers (1)

Philipp
Philipp

Reputation: 2796

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 null but COALESCE (parent.major_parent, sub.major_parent) will just skip the null and use the sub.major_parent to join the major table.

Edit

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.

Upvotes: 1

Related Questions