Hierarchical data - how to do quotwith CTEquot in mySQL

Questions

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?

Answers

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 

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/11699532/how-to-do-with-cte-in-mysql

Related