How to get all posts that have not been commented by a certain user?

Question

MySQL

Something brain explodes. I read about joins, but I'm still not sure what exactly I need them ... Please tell me where to look or maybe show some simple example. Before that, I only encountered simple data selections ..
There is a table of posts - it has:
post id,
content.
There is a table of comments - it has:
id of the author of the comment,
id of the post that is being commented on,
How can I get all the posts that have never been commented by the author with id=1?
Thanks in advance!

Answer 1

I think something like this should work

SELECT *
FROM `posts` AS p
WHERE NOT EXISTS (SELECT * FROM `comments` AS c WHERE c.user_id=1 AND p.post_id=c.post_id)

If something is wrong or I made a mistake somewhere, then let those who know correct it)))
added
it seems that without this there would be a slightly wrong result :)

Answer 2

SELECT c.id, c.text, u.id as user, p.id as post
FROM comments c
LEFT JOIN users u ON u.id=c.user_id
LEFT JOIN posts p ON p.id=c.post_id
WHERE u.id != 235

https://github.com/yiisoft/yii2/blob/master/docs/g...

Answer 3

about! cool article, I went to read, thanks

Answer 4

SELECT posts.id FROM posts
LEFT JOIN comments ON comments.author = 1 and comments.post = posts.id
WHERE comments.id IS NULL