There is the following table:

id | name | parent_id | status ---+-------+-----------+------- 1 | name1 | 0 | 1 2 | name2 | 1 | 1 3 | name3 | 1 | 1 4 | name4 | 2 | 1 5 | name5 | 3 | 1 6 | name5 | 0 | 0

I need to get all the records where status = 1 and the number of children is less than two. In other words, we need something like this:

 id | name | ะบะพะป-ะฒะพ ะดะตั‚ะตะน ---+-------+------------- 2 | name2 | ะบะพะป. ะดะตั‚ะตะน 1 3 | name3 | ะบะพะป. ะดะตั‚ะตะน 1 4 | name4 | ะบะพะป. ะดะตั‚ะตะน 0 5 | name5 | ะบะพะป. ะดะตั‚ะตะน 0

Can this be implemented in one request?

    2 answers 2

    This request will return all users whose status is 1 and children less than 2:

     SELECT * FROM users u WHERE status = 1 AND 2 > (SELECT COUNT(id) FROM users WHERE parent_id = u.id)

    Or do you need the result in this form?

     |id|name |ะบะพะป. ะดะตั‚ะตะน |2 |name2|ะบะพะป. ะดะตั‚ะตะน 1 |3 |name3|ะบะพะป. ะดะตั‚ะตะน 1 |4 |name4|ะบะพะป. ะดะตั‚ะตะน 0 |5 |name5|ะบะพะป. ะดะตั‚ะตะน 0
    • And so it will come down :) Thank you - bemulima
    • Please) - Rams666

    As an alternative solution, you can use the self-join table users .

     SELECT u.* FROM users u LEFT JOIN users p ON u.id = p.parent_id WHERE u.status = 1 GROUP BY u.id HAVING COUNT(p.id) < 2