asked
Simple SQL query from Relations.
If I have these relations: - Races(**raceId**, description) - Results(***horseName, raceId***, finishTime, prizeMoney) - Horses(**horseName**, *motherName, fatherName*) - Owners(***personName, horseName***) - Persons(**personName**) and I want to find the name of all owners that own horses who have not participated in a race. This is my solution: select personName from Owners where horseName not in (select horseName from Results); however according to the answer sheet they do like this: select distinct personName from Owners natural join Horses left outer join Results on Horses.horseName = Results.horseName where prizeMoney is null;
There are any ways to get the desire result. "NOT IN" is one of them. Beside that you can also use NOT EXISTS or LEFT OUTER JOIN. It really depends on what exactly it is asking for. For your query, you will get duplicate personName in the result as one person may own more than one horse that does not participate in a race. Adding DISTINCT to your query will fix that.
15 People are following this question.
