|
|
| Page 1 of 1 |
LeoNatan
Banned
Posts: 73193
Location: Ramat Gan, Israel 🇮🇱
|
 Posted: Wed, 30th Apr 2008 14:05 Post subject: Need some help with Oracle SQL |
|
 |
Well, I have some course that's supposed to be teaching me advanced Oracle functions and stuff, but all we do is write some SQL queries.
Anyway, I need some help with one of those queries. I need to write a query that will show pairs of employees that have the same salary, but every pair should only be displayed once.
This is what I have so far:
 Spoiler: | | Code: | SELECT DISTINCT E1.EMPLOYEE_ID, E1.FIRST_NAME, E1.LAST_NAME, E1.SALARY, E2.EMPLOYEE_ID, E2.FIRST_NAME, E2.LAST_NAME, E2.SALARY FROM EMPLOYEES E1
INNER JOIN EMPLOYEES E2
ON E1.SALARY = E2.SALARY
WHERE E1.EMPLOYEE_ID <> E2.EMPLOYEE_ID
ORDER BY E1.EMPLOYEE_ID, E2.EMPLOYEE_ID |
|
The problem is, if a pair (EMP1, EMP2) is displayed, the pair (EMP2, EMP1) is also displayed.
So I guess I need help distincting unordered pairs.
Thanks in advance!
|
|
| Back to top |
|
|
Rinze
Site Admin
Posts: 2343
|
| Posted: Wed, 30th Apr 2008 21:53 Post subject: |
|
|
No, you don't need help distincting unordered pairs, you need to think more creatively for a solution.
Don't compare employees whom you already have compared.
| Spoiler: | WHERE E1.EMPLOYEE_ID < E2.EMPLOYEE_ID |
|
|
| Back to top |
|
|
LeoNatan
Banned
Posts: 73193
Location: Ramat Gan, Israel 🇮🇱
|
| Posted: Wed, 30th Apr 2008 22:55 Post subject: |
|
|
I did think of that, and this is what I have as a solution for now, but I think it's just a convenient shortcut that would work in this specific case. I think I need a more general way of, yes  , distincting between unordered pairs.
Then again, maybe this is exactly what I needed... The other suggestions I got in other forums were all based on PL/SQL and stored procedures we haven't learned yet.
Thanks for the help Rinze!
|
|
| Back to top |
|
|
| Page 1 of 1 |
All times are GMT + 1 Hour |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB 2.0.8 © 2001, 2002 phpBB Group
|
|
 |
|
