I have three tables in my Oracle db:
Peoples:
IdPerson PK
Name
Surname
Earnings:
IdEarning PK
IdPerson
EarningValue
Awards:
IdAward PK
IdPerson FK
AwardDescription
So one person can have many earnings, one earning or can have no any earnings. Also one person can have many awards, one award, or no any award.
I have to select Surname and AwardDescription but only for people who have any earnings, because it is possible to have some award but, also don't have any earning!
My problem is to make a correct group by statement. I use query posted below and I am selecting surname of person with a description of award, but it is duplicating for each row in Earnings for this person.
SELECT AwardDescription, Surname
FROM Awards
INNER JOIN People ON People.IdPerson= Awards.IdPerson
INNER JOIN Earnings ON Earnings .IdPerson= People.IdPerson;
How to group it and avoid duplicating rows for each earning of person? One person can be in many rows, but with different awards.
You could add DISTINCT to your query:
SELECT DISTINCT AwardDescription, Surname
FROM Awards
INNER JOIN People ON People.IdPerson= Awards.IdPerson
INNER JOIN Earnings ON Earnings .IdPerson= People.IdPerson;
Or another option is to use EXISTS:
SELECT AwardDescription, Surname
FROM Awards
INNER JOIN People P ON P.IdPerson= Awards.IdPerson
WHERE EXISTS (
SELECT 1
FROM Earnings E
WHERE P.IdPerson = E.IdPerson);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments