Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.
0 votes
361 views
in Q2A Core by

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.

 

Q2A version: 1.6

Please log in or register to answer this question.

...