2

I have tried searching this site but to no avail. I do not know how to word my question quite properly, but here goes:

I have a table with employees from multiple companies in it. They all have a unique employee number (Num), but can belong to several different companies (Comp). The problem is that we have a separate entry for each company an employee is a part of. I need to be able to find every employee that has multiple entries, along with all of the companies (Comp) that they are a part of.

I cannot think of a solution short of separating all of the companies out into temp tables and comparing all of them for duplicate records.

Here is an example:

If I have the following in a table,

Comp     Num  FName  MI  LName
McD's    1    Dick   H   Harper
BrgrKng  1    Dick   H   Harper
Wendy's  2    Jane   B   Doe
Arby's   3    John   G   Doe

I want the following results:

Comp     Num  FName  MI  LName
McD's    1    Dick   H   Harper
BrgrKng  1    Dick   H   Harper

I have the following code:

SELECT Comp, Num, FName, MI, LName
FROM Employees
HAVING COUNT(Num) > 1
ORDER BY LName, FName, MI

This code does not work because of the HAVING clause and trying to display Comp as well as Num.

Whenever I try to run it I get the expected error message:

Column 'Employees.Comp' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Short of separating out all of the companies' employees and comparing every company to each, what can I do?

Side note:

None of the following entries helped:

How to Find Rows which are Duplicates by a Key but Not Duplicates in All Columns?

Find duplicates for several columns exclusive ID-column

Finding duplicate values in a SQL table

Community
  • 1
  • 1
Flu17
  • 81
  • 2
  • 8
  • 1
    A sample data with expected output will help a lot. Also please read [**How to ask**](http://stackoverflow.com/help/how-to-ask) And [**How to create a Minimal, Complete, and Verifiable example.**](http://stackoverflow.com/help/mcve) – Juan Carlos Oropeza Sep 15 '15 at 15:51
  • Does this table have a primary key? – Brian Stork Sep 15 '15 at 15:52
  • Are you looking for duplicate entries? Like; employee 1 for company A has 2 or more entries? Or do you just want to see a list of all the companies Employee1 has? – Brian Stork Sep 15 '15 at 16:03
  • Edited the question to include a sample dataset with the desired outcome. I will try to re-word my question. Brian, yes, I am looking for duplicate entries, but they have one column that will be different with each duplicate entry. – Flu17 Sep 15 '15 at 16:16

2 Answers2

7

You may try this

   SELECT Comp, Num, FName, MI, LName
    FROM Employees e1
    WHERE EXISTS(SELECT 1 FROM Employees e2 WHERE e1.Num = e2.Num AND e1.Comp <> e2. Comp)
    ORDER BY LName, FName, MI

SQL Fiddle

EricZ
  • 6,065
  • 1
  • 30
  • 30
0

try:

SELECT Comp, Num, FName, MI, LName
FROM Employees e inner join
(select num
from Employees 
group by num
having count(distinct comp)>1) d on
e.num = d.num
Beth
  • 9,531
  • 1
  • 24
  • 43