select user whose child is less than 2-CodePudding

i have table structure like this

id	user_id	parent_id	club
1	1		club1
2	2	1	club1
3	3	1	club1
4	4	2	club1
5	5	2	club1
6	6	3	club1
7	7	3	club1
8	8	4	club1
9	9	4	club1
10	10	5	club1
11	11	5	club1
12	12	6	club1
13	13	6	club1
14	14	7	club1
15	15	7	club1

i want to select user_id whose child is less then 2. refer to above table user 1,2,3,4,5,6 and 7 two child complete so the query should return 8,9,10,11,12,13,14,15

Refer to above table here user_id 1 have two child 2 and 3 , user_id 2 have child 4 and 5 and so on.

i need to select user_id whose child is less than 2

CodePudding user response：

You can do it as follows :

SELECT user_id 
FROM club_tree
WHERE user_id NOT IN (
    SELECT parent_id
    from club_tree
    where club = 'club1'
    group by parent_id 
    HAVING count(1) >= 2
) 
and club = 'club1';

We Select users that are not in the list of users with more than two childs.

This is to get users with more than two childs :

SELECT parent_id
    from club_tree
    group by parent_id 
    HAVING count(1) >= 2

Here you can test it : http://sqlfiddle.com/#!9/eb2c70/3

CodePudding user response：

Can you try this query using left join :

SELECT c.user_id
  from club_tree c
  left join 
  (
    select parent_id
    from club_tree
    where club = 'club1'
    group by parent_id 
    HAVING count(1) >= 2
  ) as c2 on c.user_id = c2.parent_id
  where c.club = 'club1' and c2.parent_id is null;

Try it here : http://sqlfiddle.com/#!9/eb2c70/17