SQL server find Duplicates in column 1 while ask for 2 specific values in column 2

Question

I have a table like this:

Id  first_name  department
1   John        IT
2   George      Support
3   Jack        IT
4   Jack        IT
5   George      Dev
6   Maria       Dev
7   George      IT

I am trying to get this:

Id  first_name  department
5   George      Dev
7   George      IT

I am stuck an this:

SELECT * 
FROM tbl_employees 
WHERE first_name in ( SELECT first_name FROM tbl_employees where (department = 'IT' or department =  'Dev') 
GROUP BY first_name 
HAVING count( first_name ) > 1 )

Thx M.Ali Could not find the look for table – phoenix epsilon Sep 14 '14 at 12:47 — phoenix epsilon, Sep 14 '14 at 12:47
Are you using SQL Server or MySQL? – Gordon Linoff Sep 14 '14 at 12:56 — Gordon Linoff, Sep 14 '14 at 12:56

score 1 · Answer 1 · answered Sep 14 '14 at 12:58

If you are trying to get the original rows, you can do something like this:

select e.*
from tbl_employees e
where e.department in ('IT', 'DEV') and
      exists (select 1 from tbl_employees e2 where e2.first_name = e.first_name and e2.department = 'IT') and
      exists (select 1 from tbl_employees e2 where e2.first_name = e.first_name and e2.department = 'DEV');

M.Ali · Accepted Answer · 2014-09-14T13:18:31.050

SQL Fiddle

MS SQL Server 2008 Schema Setup:

Query 1:

DECLARE @TABLE TABLE(Id INT, first_name VARCHAR(20), department VARCHAR(20))
INSERT INTO @TABLE VALUES
(1   ,'John',   'IT'),
(2   ,'George', 'Support'),
(3   ,'Jack',   'IT'),
(4   ,'Jack',   'IT'),
(5   ,'George', 'Dev'),
(6   ,'Maria',  'Dev'),
(7   ,'George', 'IT'),
(8  ,'Maria',  'Support')

SELECT * FROM @TABLE 
WHERE first_name IN (
                    SELECT first_name FROM @TABLE WHERE department = 'Dev'
                    INTERSECT 
                    SELECT first_name FROM @TABLE WHERE department = 'IT'
                    )
AND department IN ('Dev', 'IT')

Results:

| ID | FIRST_NAME | DEPARTMENT |
|----|------------|------------|
|  5 |     George |        Dev |
|  7 |     George |         IT |

I cant vote up cause of rep thank you so much for the answer works. PS you could skip the part of creating the table but thats how your is so complete I am in debt mate — phoenix epsilon, Sep 14 '14 at 15:09

Pedro Parada · Answer 3 · 2014-09-14T23:16:27.170

You could try this

    SELECT T.first_name,T.department
    FROM @TABLE T
    INNER JOIN  
   (        SELECT Id, first_name
            FROM @TABLE T2
            WHERE EXISTS ( 
                         SELECT 1 FROM @TABLE T3 
                         WHERE T3.first_name = T2.first_name and T3.department = 'Dev'
                         )
            AND   EXISTS ( SELECT 1 FROM @TABLE T4 
                         WHERE T4.first_name = T2.first_name and T4.department = 'IT'
                         )
      ) ElementsBelongingToBothDevAndIT ON ElementsBelongingToBothDevAndIT.Id = T.Id
    INNER JOIN 
   (
          SELECT first_name
          FROM @TABLE T2
          WHERE EXISTS (
                        SELECT 1 FROM @TABLE T3 
                        WHERE T3.first_name = T2.first_name and T3.department = 'Dev'
                       )
          AND   EXISTS ( 
                        SELECT 1 FROM @TABLE T4 
                        WHERE T4.first_name = T2.first_name and   T4.department = 'IT'
                       ) 
          GROUP BY first_name
           HAVING COUNT(*) > 1
  ) ElementsBelongingToBothDevAndITHavingDuplicates 
    ON ElementsBelongingToBothDevAndITHavingDuplicates.first_name=
    ElementsBelongingToBothDevAndIT.first_name
 WHERE T.department IN ('Dev','IT')
 GROUP BY T.first_name, T.department

Sorry mate i lack the patience to try this out since I found a more convenient one. Thank anyway — phoenix epsilon, Oct 03 '14 at 17:50

score -1 · Answer 4 · answered Oct 05 '14 at 17:32

After much effort I found another solution to my own problem which is better for generating the query in C#. Also I had a last_name column added so it complicated things a bit when i used intersect.

So I ended up with this

SQL Fiddle

Query 1:

DECLARE @TABLE TABLE(Id INT, first_name VARCHAR(20), department VARCHAR(20))
INSERT INTO @TABLE VALUES
(1   ,'John',   'IT'),
(2   ,'George', 'Support'),
(3   ,'Jack',   'IT'),
(4   ,'Jack',   'IT'),
(5   ,'George', 'Dev'),
(6   ,'Maria',  'Dev'),
(7   ,'George', 'IT'),
(8  ,'Maria',  'Support')


 SELECT y.Id,y.first_name,y.department
 FROM @table y 
 INNER JOIN (SELECT first_name, COUNT(*) AS CountOf, COUNT(distinct department) as CountDep
             FROM @table
             WHERE department in  ('IT','Dev')
             GROUP BY first_name
             HAVING COUNT(*) > 1 and COUNT(distinct department) > 1
             ) dt ON y.first_name=dt.first_name 
             and department in ('IT','Dev')
    order by first_name

Results:

| ID | FIRST_NAME | DEPARTMENT |
|----|------------|------------|
|  5 |     George |        Dev |
|  7 |     George |         IT |

Cause I pass the search parameters from a checkbox list its easier to manipulate the departments and the number of them.Furthermore if I want to find duplicates in one department all i have to do is put >0 in the COUNT department section

SQL server find Duplicates in column 1 while ask for 2 specific values in column 2

4 Answers4