Clever Geek Handbook

How can I use a returned claim to join two tables in a third table?

I have a comment table with these fields

id   | user_id | parent_id

and user table

id   | username

how sholud I join this table with myself and the user table to get the username of the parent comment?

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 the first connection i get child_user_id which is the user id i want is the username. But how can I join the users table based on child_user_id?

+3


source to share


1 answer


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
+1


source






More articles:


All Articles