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

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

1 Answer

+1 vote
by

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
 
by
thanks but, I have now moved on to other methods.
...