Question 21470 in Mysql Question posted in
The official MySQL documentation can be found here.

select user whose child is less than 2 – Mysql

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

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search