1

I have a database table (Support_help_topic) which contains the following data:

topic_id | topic_pid | topic

What I want to be able to do is select a help topic, then get all its parent topics and concatenate them together.

Take the Following Example.

There are several parent help topics: Incidents, Requests, General Support

Each of these parent topics may have several child topics and the child topics may have additional topics under them.

The below is an example of a full help topic path

Incident / Software / Microsoft Outlook

Incident might have a

  • topic_id of 1
  • topic_pid of 0 (its a top level topic)

Software might have a

  • topic_id of 20
  • topic_pid of 1 (its parent is Incident)

Microsoft outlook might have a

  • topic_id of 34
  • topic_pid of 20 (its parent is Software)

I need to in each case get either of the following in a single select statement:

If the topic has parents i need to get: - Top_Level_Topic / Topic

If the topic has no parents i need to get:

  • Topic

I am so stuck on this I have no idea where to begin.

Thanks in advance for any assistance. More than happy to provide an additional info needed.

Lewis M Hackfath
  • 131
  • 5
  • 17
  • http://www.mysqltutorial.org/mysql-adjacency-list-tree/ – Barmar Jan 23 '19 at 21:19
  • Unfortunately I may not be able to use this method as we are still on mysql 5.7 – Lewis M Hackfath Jan 23 '19 at 21:38
  • 1
    Didn't realize that article used CTE (I just found it because an older article has gone away). Take a look at https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query – Barmar Jan 23 '19 at 21:53

1 Answers1

0

I ended up using the following:

SELECT
    CASE
        WHEN t2.topic IS NULL then t1.topic
        WHEN t3.topic IS NULL then concat(t2.topic, ' / ', t1.topic)
        ELSE concat(t3.topic, ' / ', t2.topic, ' / ', t1.topic)
    END as help_topic
FROM 
    Support_help_topic AS t1
LEFT JOIN 
    Support_help_topic AS t2 ON t2.topic_id = t1.topic_pid
LEFT JOIN 
    Support_help_topic AS t3 ON t3.topic_id = t2.topic_pid
where 
    t1.topic_id = `supptick`.`topic_id`
Lewis M Hackfath
  • 131
  • 5
  • 17