CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Monowar
Monowar

Reputation: 106

Return all groups that have exactly the same employees

There are 2 tables:

Employee
(id_employee, worker_name)

Groups
(id_employee, group_name)

Here is creation script for you:

CREATE TABLE Employee (
   id_employee int identity(1,1) NOT NULL CONSTRAINT PK_Employee PRIMARY KEY CLUSTERED,
   worker_name nvarchar(100) CONSTRAINT UQ_Employee_worker_name UNIQUE
);

CREATE TABLE Groups (
   id_employee int NOT NULL CONSTRAINT FK_Groups_id_employee FOREIGN KEY REFERENCES Employee (id_employee),
   group_name varchar(10) NOT NULL,
   CONSTRAINT PK_Groups PRIMARY KEY CLUSTERED (group_name, id_employee)
);

INSERT Employee
SELECT 'worker 1'
UNION ALL SELECT 'worker 2'
UNION ALL SELECT 'worker 3'
UNION ALL SELECT 'worker 4';

INSERT Groups
SELECT 1, 'a1'
UNION ALL SELECT 2, 'a1'
UNION ALL SELECT 3, 'a2'
UNION ALL SELECT 4, 'a2'
UNION ALL SELECT 1, 'b1'
UNION ALL SELECT 2, 'b1'
UNION ALL SELECT 3, 'b2'
UNION ALL SELECT 4, 'b2'
UNION ALL SELECT 2, 'b3'
UNION ALL SELECT 3, 'b3'
UNION ALL SELECT 4, 'b3';

I need a query that for a given id_group will return all other groups that have exactly the same employees.

For example:

SELECT for 'a1'

-> Should return 'b1' because in both groups there are: worker 1 & worker 2

SELECT for 'a2'

-> Should return 'b2' because in both groups there are: worker 3 & worker 4

Note that groups need to be exactly the same, all members of a1 need to be in b1 and size of both groups need to be the same as well.

Upvotes: 2

Views: 251

Answers (5)

AnandPhadke
AnandPhadke

Reputation: 13506

Here is the sql query to get the desired result.

DECLARE @group_name varchar(10)='a1'
;WITH CTE(group_name,emp_ids) as (select group_name,(SELECT  cast(id_employee as varchar(10))+ ', ' as [text()]
    FROM    Groups where group_name=g.group_name 
    ORDER BY id_employee DESC
    FOR XML PATH('') )  as emp_ids from Groups g group by group_name)

SELECT group_name FROM CTE where emp_ids in(select emp_ids from CTE where group_name = @group_name)
and group_name <> @group_name

Upvotes: 0

ErikE
ErikE

Reputation: 50241

This works. See the setup script I added to the question if you want to try it.

SELECT DISTINCT G.group_name
FROM Groups G
WHERE
   G.group_name <> @group
   AND NOT EXISTS (
      SELECT *
      FROM
         (SELECT * FROM Groups WHERE group_name = @group) G1
         FULL JOIN (SELECT * FROM Groups WHERE G.group_name = group_name) G2
            ON G1.id_employee = G2.id_employee
      WHERE EXISTS (SELECT G1.id_employee EXCEPT SELECT G2.id_employee)
   );

Upvotes: 0

shawnt00
shawnt00

Reputation: 17915

It took a few edits to get the one I wanted

with pairs as (
    select gs1.group_name as group1, gs2.group_name as group2, gs1.emp_count as emp_count
    from
    (select group_name, count(1) as emp_count from groups group by group_name) as gs1 inner join
    (select group_name, count(1) as emp_count from groups group by group_name) as gs2
        /* choose parameterized */
        --on gs1.group_name = 'a1' and gs1.group_name != gs2.group_name and gs1.emp_count = gs2.emp_count
        /* or all pairs */
        on gs1.group_name < gs2.group_name and gs1.emp_count = gs2.emp_count
)
select
    pairs.group1, pairs.group2
from
    pairs inner join

    (select group_name, id_employee from Groups) as g1
        on g1.group_name = pairs.group1 inner join

    (select group_name, id_employee from Groups) as g2
        on g2.group_name = pairs.group2 and g1.id_employee = g2.id_employee
group by
    pairs.group1, pairs.group2
having
    min(pairs.emp_count) = count(g1.group_name);

Upvotes: 0

Zane Bien
Zane Bien

Reputation: 23125

You can try this solution: 

SELECT b.group_name
FROM
(
    SELECT aa.id_employee, aa.group_name, bb.group_count
    FROM groups aa
    CROSS JOIN
    (
        SELECT COUNT(1) AS group_count FROM groups WHERE group_name = 'a1'  
    ) bb
    WHERE aa.group_name = 'a1'
) a
INNER JOIN groups b ON a.id_employee = b.id_employee AND a.group_name <> b.group_name
INNER JOIN
(
    SELECT group_name, COUNT(1) AS other_group_count
    FROM groups
    GROUP BY group_name
) c ON b.group_name = c.group_name
WHERE a.group_count = c.other_group_count
GROUP BY b.group_name, a.group_count
HAVING COUNT(1) = a.group_count

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You can use SQL set operations to do the work. The idea is to match employees together within groups and to count the totals in the groups.

select others.group_name
from (select g.*, count(*) over (partition by group_name) as numemps
      from groups g
      where group_name <> @group
     ) others left outer join
     (select g.*, count(*) over (partition by group_name) as numemps
      from groups g
      where group_name = @group
     ) thegroup
     on others.id_employee = thegroup.id_employee and
        others.numemps = thegroup.numemps
group by others.group_name
having count(*) = max(thegroup.numemps) and
       max(case when thegroup.id_employee is null then 1 else 0 end) = 0

So, this query divides the world into two . . . your group and all the other groups. It then matches them by name and aggregates by groups. The only candidate groups are the ones with the same numbers of employees.

The having clause chooses the groups that match. This means that every name in the other group matches a name in the group you care about. Since the sizes of the groups are the same, and each name in your group is matched, the groups contain the same employees.

Upvotes: 2

Related Questions