CODEWITHSUNDEEP
sqlsql-serverhaving
Lucas Araujo
Lucas Araujo

Reputation: 143

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

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

Upvotes: 1

Views: 81

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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;

Upvotes: 4

Related Questions