How to create a query with a criterion in another table?

Question

MySQL

Good afternoon friends.
Initial data:
Table Users
user_id name
1 Vitya
2 Ivan
3 Vasya
Children table (children) contains id, id of the parent (user_id), as well as a flag indicating whether the current child has children (has_child 1-yes, 0-no)
ch_id user_id has_child
1 | 1 | 0
2 | 1 | 0
3 | 2 | 1
4 | 2 | 0
5 } 2 | one
My task is to get user IDs from the first table who do not have grandchildren. Accordingly, users Vitya and Vasya (id = 1, id = 3) will get here. Vitya has two children, but these children do not have children of their own. Vasya just does not have children, respectively, and no grandchildren.
So far, I have solved this problem in two stages. The first one got a list of all users, and then looped through the children table with such SELECT * from children WHERE user_id = : user_id AND has_child = 1, if at least one record was returned, then the user has grandchildren and I don’t need him.
Now the tables have become large, and it has become costly to shove the query inside the cycle.
Thanks in advance for your help

Answer 1

SELECT u.*, COUNT(c.has_child) as sum, SUM(c.has_child) as sum2 FROM users as u LEFT JOIN children as c ON (c.user_id = u.id) GROUP BY c.user_id HAVING (sum = 0 OR sum2 = 0)

The speed of work will most likely be sad.
It depends on which application, if momentary data is important, then you will have to deal with such requests.
If updating the data is not very critical, then you can add the is_grandpa field to the table and update this field by cron once a minute, either as it was updated earlier, or by my request above. And the fetch request will be simplified to ridiculous )

Answer 2

there is a series of good articles on habré habrahabr.ru/post/190396

Answer 3

I think Tor is enough for you.