Select data from two records in same table in a single query using MYSQL and PHP

I have a MYSQL query involving JOINS and Union statements whereby I query multiple tables to produce a JSON feed. (I am weak on MYSQL so apologies in advance.)

I now have to add comments. If the comment is about something in a separate table such as Articles, then you can do a JOIN on the two tables.

The problem I'm having is if the comment is on a comment, I need information from two rows of the sambe table, the new comment row 2--as well as the original comment row 1 that the new comment is about. I don't how to get data from 2 rows of one table in a single query. I'm hoping there is a way or some other strategy that would do this.

Here is a simplified version of the query.

Articles
id|text|pic|authorid

Users
id|name|pic

Comments
id|articleid|userid|commentid|text

SELECT a.id as 'itemid',a.text as 'body',a.pic as 'itempic',u.name as 'authorname',u.pic as 'authorpic', nil as 'subtext' 
FROM Articles `a`
LEFT JOIN 
Users `u` 
ON a.authorid = u.id 
UNION
Select c.id as 'itemid',c.comment as 'body',nil as 'itempic',u.name as 'authorname',u.pic as 'authorpic', cm.text as 'subtext' 
FROM COMMENTS `c`
/* THIS APPROACH DOES NOT SEEM TO WORK *?
LEFT JOIN 
COMMENTS `cm` 
ON cm.id = c.id
/* end approach */
LEFT JOIN 
Users `u` 
ON c.userid = u.id 
WHERE c.articleid = a.id 
GROUP by itemid

Am I on the right track here or how can you hit one table twice with the same query. Thanks for any suggestions.

Solution

Good day. Firstly, I would like to clarify if I got your thought process right.

You want to fetch all comments belonging to a post based on the Article ID if yes, you could use this SQL query to fetch all comments belonging to the article. The main criteria are joining the comments and article and then users to comments because the article has one to many relationships with comments and comment has one to one relationship with users.

So, use the left join to fetch all comments from the article and join the user to comments based on the user ID on the comments table.

A sample Code and visual are presented below

SELECT  A.id as 'itemid',
        A.text as 'body',
        A.pic as 'itempic',
        U.name as 'authorname',
        U.pic as 'authorpic',
        C.id as 'commentId',
        C.text as 'commentBody'
FROM Article AS A
LEFT JOIN Comments AS C 
    ON C.articleid = A.id 
JOIN users AS U
    ON U.id =c.userid
WHERE A.id = 1;

Sample Image result

You can tweak the value picked from the query based on your design. I hope it helps.

Thanks