SQL Server 2014 duplicates

Question

I have 3 tables and I am trying to find the duplicates. There is a small condition that is throwing me off. These are the 3 tables

Now I need to run a query to get all institutions for GroupID = 100 and also return any duplicate institutions based on the InstitutionName. So my result set should include both InstitutionID = 1 and 2 since they are dupes on Name even though only one of them belongs to the group 100.

The query should not return data for InstitutionID = 3 because it isn't a duplicate on Name even though it belongs to the Group 100.

Answer 1

Using first two tables institution and department is enough, where use in operator and exists clause :

select *
  from institution
 where institutionname in 
      (
        select i.institutionname
          from institution i 
         where exists ( select 1 from department d where groupid = 100  ) 
         group by i.institutionname
         having count(i.institutionname)>1
      );

institutionid   institutionname deparmentid
1               ABC             123
2               ABC             456

Edit : I think you want this as more(by using "group" table also) :

with institution( institutionid, institutionname, deparmentid, city ) as
(
 select 1,'ABC',123,'New York' union all
 select 2,'ABC',456,'New York' union all
 select 3,'DEF',123,'Chicago'  union all
 select 4,'GHI',789,'Los Angeles'
),    
     department( deparmentid, deparmentname, groupid ) as
(
 select 123,'dept1',100 union all
 select 456,'dept2',101 union all
 select 789,'dept3',102 
),
     "group"( groupid, groupname ) as
(
 select 100,'Group A'     
)    
    select ii.*, g.groupname, dd.deparmentname
      from institution ii
      left join department dd on dd.deparmentid = ii.deparmentid
      left join "group" g on g.groupid = dd.groupid
     where ii.institutionname in 
          (
            select i.institutionname
              from institution i 
             where exists ( select 1 from department d where d.groupid = 100  ) 
             group by i.institutionname
             having count(i.institutionname)>1 and count(i.city)>1
          );

institutionid   institutionname deparmentid city      groupname deparmentname
1               ABC             123         New York  Group A   dept1
2               ABC             456         New York  NULL      dept2

Answer 2

You can use a subquery to get the names of institutions belonging in group = 100 and then with in get the rows from institution:

select * from institution
where name in (
  select i.name
  from institution i inner join department d
  on d.departmentid = i.departmentid
  inner join group g
  on g.groupid = d.groupid
  where g.groupid = 100
)