JOIN qa_posts table to itself

Question

Hi, I'm trying to produce a mysql select which will find all Q, A, C in qa_posts but, I noticed that the parentid for the A, C is in the same table as the Q and I'm struggling a bit to get the select correct. Here's what I have so far:

I'm tyring to get postid | title | type | parentid - if A or C, there is no title so tried to get title of question using a join.

 

WITHOUT JOIN

 

SELECT qa_posts.postid, qa_posts.title, qa_posts.type, qa_posts.parentid

FROM qa_posts

ORDER BY qa_posts.created DESC

 

TRIED THIS JOIN, BUT ALL MESSED UP

 

SELECT qa_posts.postid, qa_posts.title, qa_posts.type, qa_posts.parentid

FROM qa_posts

JOIN qa_posts AS p ON p.parentid = qa_posts.postid

ORDER BY qa_posts.created DESC

Answer 1

Give this a try:

SELECT child.postid, COALESCE(child.title, parent.title) title, child.type, child.parentid

FROM qa_posts child

LEFT JOIN qa_posts parent ON parent.postid = child.parentid

ORDER BY child.created DESC

 

Comments

thanks but, I have now moved on to other methods.