1

I have table 'CATEGORY' something like this

| NUMBER | NAME | TITLE | UP_CATEGORY |
 -------------------------------------
|  1     | AAA  |       |      0      |
 -------------------------------------
|  2     | BBB  |       |      0      |
 -------------------------------------
|  3     | CCC  |       |      1      |
 -------------------------------------
|  4     | DDD  |       |      2      |
 -------------------------------------
|  5     | EEE  |       |      3      |
 -------------------------------------

I want to have titles like a breadcrumbs in webpage navigation (main page > category 1 > category 2). In this case titles should look like:

AAA = AAA
BBB = BBB
CCC = CCC :: AAA
DDD = DDD :: BBB
EEE = EEE :: CCC :: AAA

Is this even possible to do in pure MySQL? I searched for self join but with no goood results.

I've tried something like this and did not work:

UPDATE t1 SET t1.title = t2.name
FROM category t1, category t2 WHERE t2.up_category = t1.number

Am I going in good way to achieve what I want?

EDIT:

@Alex, I do not think my question is duplicate of How to create a MySQL hierarchical recursive query, but in some way similar. It helped me with making a proper select to achieve what I want (using Managing Hierarchical Data in MySQL).

The code for SELECT:

SELECT CONCAT_WS(" :: ",t3.name,t2.name,t1.name), t3.number, t2.number, t1.number
FROM category AS t1
LEFT JOIN category  AS t2 ON t2.up_category = t1.number
LEFT JOIN category  AS t3 ON t3.up_category = t2.number
WHERE t1.up_category = 0

So I have now an result with 4 columns - 1th column - my proper 'breadcrumbs' - 2nd column - third child's id (number or NULL, becouse sometimes there is parent and only one child) - 3nd column - second child's id (number) - 4th column - first child's id (number)

Now I'm thinking about updating table with results from my select, but for now I don't know how to perform this.

Kamil Czubak
  • 64
  • 7
  • Where is the update part? And what have you tried so far? – Lelio Faieta Jan 10 '18 at 16:48
  • @LelioFaieta I doubt that he requires update part at all. I guess the answer of Val Bonn is on the right way. – asd-tm Jan 10 '18 at 17:35
  • Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) – Alex Jan 11 '18 at 00:04
  • @KamilCzubak Good thing you solved it, but you should put the solution into an answer instead of the question. (Self-answers are encouraged here.) – Hermann Döppes Jan 21 '18 at 13:29
  • @Hermann Döppes thank you, I corrected my question and answered below. – Kamil Czubak Jan 22 '18 at 11:36

1 Answers1

0

I already managed to do it. Solution below.

UPDATE category table1, 
(
    SELECT CONCAT_WS(" :: ",t3.name,t2.name,t1.name) AS title, IF(t3.number IS NOT NULL,t3.number,IF(t2.number IS NOT NULL,t2.number,t1.number)) AS id_cat
    FROM category AS t1
    RIGHT JOIN category AS t2 ON t2.up_category = t1.number
    RIGHT JOIN category AS t3 ON t3.up_category = t2.number
 ) table2

SET table.title = table2.new_title
WHERE table1.number = table2.id_cat

I think it covers all. One way or another works so it's good.

Kamil Czubak
  • 64
  • 7