How to get data from 4 tables in one query?

Question

PHP

There are tables The

request should start from the comments table, the last 10 is taken. It is
possible to get data from 4 tables in one request, while the amount depends on the value (is_read takes two values ​​\u200b\u200bof 0 or 1) That is, the number of 0 and 1 separately for each user. From table 4, the result should approximately be
comments.text, users.name, avatars.link, count.
Count is the number of read (0) and unread (1) posts.
I solve all this through PHP, making separate requests for comparing arrays and doing concatenation.
Here's what I'm getting now
Separately I get I
SELECT * FROM comments;
Get the number and username from users

select count(is_read), post.user_id, users.name,  FROM post INNER JOIN users ON users.id=post.user_id  WHERE is_read=1 AND color='black' group by user_id

Can't get data from avatars table

Answer 1

With the new conditions, look at something like this:

select post.user_id, count(*) as c_all, count(if(post.is_read = 0, 1, NULL)) as c_not, count(if(post.is_read = 0, NULL, 1)) as c_read 
from post 
where post.user_id in (...)
group by post.user_id

With your first request (SELECT * FROM comments ORDER BY id DESC LIMIT 10) you get user IDs, which you then put in the second request. in the second you get counters. You only need to add joins for additional information.
When displaying comments in php, display comments as comments, and when you have information about a user using his ID, you access the array from the second request.
And it’s better not to change the conditions, but create a new question, otherwise it turns out stupid, you answer one thing, and then the question is completely different