Lucas Araujo Lucas Araujo - 5 months ago 10
SQL Question

How do I put two conditions in having in SQL Server?

I have to get members that are on five different groups and those groups should have 5 members each


  • Member
    (idMember, nameMember)

  • Group
    (idGroup)

  • Belongs
    (idMember, idGroup)



Groups have members, members are in groups, a member can be in as many groups as he wants but a member can't be twice on the same group

I made something like

select idMember, nameMember
from Member m, Group g
where idMember in (select b.idMember
from Belongs b)
group by idMember, nameMember
having (select count(*)
from Belongs b
where b.idMember = m.idMember)>5
and
(select count (*)
from Belongs b
where b.idGroup = g.idGroup /*??*/)>5


and there I don't know how to relate belongs with group

Answer

Approach this type of problem in steps. The following gets the members that are in five groups:

select b.idMember
from belongs b  -- Note:  `group` is a reserved word so a bad name for a table
group by b.idMember
having count(*) = 5;

The following gets the groups that have five members:

select b.idGroup
from belongs b 
group by b.idGroup
having count(*) = 5;

(Interesting symmetry.)

If you want to limit the first query to the groups in the second, then a simple way is to use in:

select b.idMember
from belongs b
where b.groupId in (select g.idGroup
                    from belongs b
                    group by b.idGroup
                    having count(*) = 5
                   )
group by b.idMember
having count(*) = 5;

When you are dealing with complex queries, build them up one step at a time.

Note: group is a really bad name for a table because it is a reserved word. And, if you want the table name then use JOIN to join to the members table to get the right name.

EDIT:

You can use join to get columns from member:

select b.idMember, m.name
from belongs b join
     member m
     on b.idMember = m.idMember
where b.groupId in (select g.idGroup
                    from belongs b
                    group by b.idGroup
                    having count(*) = 5
                   )
group by b.idMember, m.name
having count(*) = 5;
Comments