What is the problem this query؟
select postid, parentid, type
from (
SELECT q1.*
, lpad(q1.parentid,8,case q1.type when 'Q' then 'a' when 'C' then 'b' when 'A' then 'c' end)
|| lpad(q1.postid,8,case q1.type when 'Q' then 'a' when 'C' then 'b' when 'A' then 'c' end) as padded_path
FROM qa_posts q1 WHERE q1.postid=1
UNION
SELECT q2.*
, lpad(q1.parentid,8,case q1.type when 'Q' then 'a' when 'C' then 'b' when 'A' then 'c' end)
|| lpad(q1.postid,8,case q1.type when 'Q' then 'a' when 'C' then 'b' when 'A' then 'c' end)
|| lpad(q2.postid,8,case q2.type when 'Q' then 'a'
when 'C' then 'b'
when 'A' then 'c' end) as padded_path
FROM qa_posts q1
JOIN qa_posts q2
on q1.postid=q2.parentid
WHERE q1.postid=1
UNION
SELECT q3.*
, lpad(q1.parentid,8,case q1.type when 'Q' then 'a'
when 'C' then 'b'
when 'A' then 'c' end)
|| lpad(q1.postid,8,case q1.type when 'Q' then 'a'
when 'C' then 'b'
when 'A' then 'c' end)
|| lpad(q2.postid,8,case q2.type when 'Q' then 'a'
when 'C' then 'b'
when 'A' then 'c' end)
|| lpad(q3.postid,8,case q3.type when 'Q' then 'a'
when 'C' then 'b'
when 'A' then 'c' end) as padded_path
FROM qa_posts q1
JOIN qa_posts q2
on q1.postid=q2.parentid
JOIN qa_posts q3
on q2.postid=q3.parentid
WHERE q1.postid=1
) as x
order by padded_path;
I need result like this table
+--------+----------+------+----------------------------------+
| postid | parentid | type | padded_path |
+--------+----------+------+----------------------------------+
| 1 | 0 | Q | aaaaaaa0aaaaaaa1 |
| 8 | 1 | C | aaaaaaa0aaaaaaa1bbbbbbb8 |
| 2 | 1 | A | aaaaaaa0aaaaaaa1ccccccc2 |
| 4 | 2 | C | aaaaaaa0aaaaaaa1ccccccc2bbbbbbb4 |
| 5 | 2 | C | aaaaaaa0aaaaaaa1ccccccc2bbbbbbb5 |
| 6 | 1 | A | aaaaaaa0aaaaaaa1ccccccc6 |
+--------+----------+------+----------------------------------+
padded_path field is filled with NULL values.
