how can use returned filed of two table join in third table?

Question

i have a comment table with this fields

id   | user_id | parent_id 

and a user table

id   | username

how sholud i join this table with itself and user table to get parent comment user name?

SELECT comment.* ,c1.id as child_id,c1.user_id as child_user_id FROM `comment`
LEFT JOIN comment c1 ON c1.parent=comment.id
LEFT JOIN users ON users.id=child_user_id

in first join i get child_user_id which is a user id that i want it's user name But how can I join user table based on child_user_id?

Answer 1

Try something like this:

SELECT *,(
    SELECT username FROM user WHERE id = a.parent_id
) parent_username
FROM comment a
JOIN user b on a.user_id = b.id