how to do "with CTE" in mySQL?

Question

I have a table "posts" in MYSQL for some QA forum (for eg. stackoverflow), with the following sample data.

    INSERT INTO POSTS (postID, type, parentID, QuesTitle) VALUES(1,'Q',NULL,'sometext');
    INSERT INTO POSTS (postID, type, parentID, QuesTitle) VALUES(2,'Q',NULL,'randomtext');
    INSERT INTO POSTS (postID, type, parentID, QuesTitle) VALUES(3,'A',1,NULL);
    INSERT INTO POSTS (postID, type, parentID, QuesTitle) VALUES(4,'A',1,NULL);
    INSERT INTO POSTS (postID, type, parentID, QuesTitle) VALUES(5,'Q',NULL,'titletext');
    INSERT INTO POSTS (postID, type, parentID, QuesTitle) VALUES(6,'A',1,NULL);
    INSERT INTO POSTS (postID, type, parentID, QuesTitle) VALUES(7,'A',2,NULL);
    INSERT INTO POSTS (postID, type, parentID, QuesTitle) VALUES(8,'A',2,NULL);

postID is primary key and parentID refers to postID. A "Q" (question) type post can have multiple "A" (answers) posts(shown by their parentID). Only "Q" posts have titles but "A" posts have no titles(NULL)

I want to select the "A" type posts along with their post ID, their parent ID and the Questitle it refers to. For example- required result is-

    postID    parentID    QuesTitle
    3         1           sometext
    4         1           sometext
    6         1           sometext
    7         2           randomtext
    8         2           randomtext

It would be easier using 'with' clause CTE but MYSQL doesn't support it. Can someone help me in achieving it in MYSQL?

Answer 1

In their example it may not be clear as to why they would want to use a CTE, but I agree that it would be really nice if MySQL would add this sorely lacking feature (hint hint MariaDB foundation)

CTE's can be really useful for arbitrary depth hierarchical data.

Here is a postgres example:

CREATE TABLE posts (
    postid integer,
    type character varying(100),
    parentid integer,
    questitle character varying(100)
);
INSERT INTO posts VALUES (1, 'Q', NULL, 'sometext');
INSERT INTO posts VALUES (2, 'Q', NULL, 'randomtext');
INSERT INTO posts VALUES (3, 'A', 1, NULL);
INSERT INTO posts VALUES (4, 'A', 1, NULL);
INSERT INTO posts VALUES (5, 'Q', NULL, 'titletext');
INSERT INTO posts VALUES (6, 'A', 1, NULL);
INSERT INTO posts VALUES (7, 'A', 2, NULL);
INSERT INTO posts VALUES (8, 'A', 2, NULL);
INSERT INTO posts VALUES (9, 'A', 8, NULL);
INSERT INTO posts VALUES (10, 'A', 4, NULL);
INSERT INTO posts VALUES (11, 'A', 10, NULL);
INSERT INTO posts VALUES (12, 'A', 11, NULL);

with the following query:

with recursive posts_cte (postid, type, parentid, questitle,level,id_lineage)  as
(
    -- base case
    select 
        postid, type, parentid, questitle, 0 as level, ''||postid as id_lineage
    from 
        POSTS
    where 
        parentid is null

    UNION ALL

    -- recursion case
    select 
        c.postid, c.type, c.parentid, c.questitle, p.level+1 as level, p.id_lineage||','||c.postid as id_lineage
    from
        posts c
        inner join posts_cte p on
            c.parentid = p.postid
)
select
    postid, type, parentid, questitle,level,id_lineage
from
    posts_cte
order by
    id_lineage;

yields the result set:

 postid | type | parentid | questitle  | level |  id_lineage
--------+------+----------+------------+-------+--------------
      1 | Q    |          | sometext   |     0 | 1
      3 | A    |        1 |            |     1 | 1,3
      4 | A    |        1 |            |     1 | 1,4
     10 | A    |        4 |            |     2 | 1,4,10
     11 | A    |       10 |            |     3 | 1,4,10,11
     12 | A    |       11 |            |     4 | 1,4,10,11,12
      6 | A    |        1 |            |     1 | 1,6
      2 | Q    |          | randomtext |     0 | 2
      7 | A    |        2 |            |     1 | 2,7
      8 | A    |        2 |            |     1 | 2,8
      9 | A    |        8 |            |     2 | 2,8,9
      5 | Q    |          | titletext  |     0 | 5

Answer 2

Why would you need a CTE?

You can simply do a self-join to get the parentID's information:

SELECT a.postID, a.parentID, b.QuesTitle
FROM   posts a
JOIN   posts b ON a.parentID = b.postID
WHERE  a.type = 'A'