CODEWITHSUNDEEP
oracle11g
Manick
Manick

Reputation: 3

Self join to get names of employees getting same salary

I want to get the name of employees who gets same salary from employees table in different columns and their common salary in separate column. I have used the following query using Self Join.

SELECT a.first_name AS "Employee 1", b.first_name AS "Employee 2", 
       a.salary AS "Salary" FROM employees a, employees b

WHERE a.salary=b.salary AND a.first_name <> b.first_name;

The problem with the above query is duplicate records. For example, a and b appears in a row and then b and a appears in some other row. How can I avoid this?

Upvotes: 0

Views: 4227

Answers (1)

GrandOpener
GrandOpener

Reputation: 2123

You are getting duplicates because you have requested all results where the names are not equal in any order. If you only want one copy, specify an order:

SELECT a.first_name AS "Employee 1", b.first_name AS "Employee 2", a.salary AS "Salary" 
  FROM employees a, employees b 
 WHERE a.salary = b.salary AND a.first_name > b.first_name;

Note the greater than comparator rather than not-equal comparator in the last condition.

Note: you still might have a problem here. If two employees have the same first name, this will not find them. An even better solution would compare some unique identifier:

SELECT a.first_name AS "Employee 1", b.first_name AS "Employee 2", a.salary AS "Salary" 
  FROM employees a, employees b 
 WHERE a.salary = b.salary AND a.employee_id > b.employee_id;

Upvotes: 1

Related Questions