I have the following data modeling:
And they ask me for the following query
perform a query that returns the name and surname of the athletes who have participated in a competition, the description of said competition, the date on which the athlete participated and the stadium where it was held, for those competitions in which athletes who have participated are federated in the 'Union Cycliste Internationale'. Additionally, it is desired that the results be filtered to show only the male athletes, who have won the Gold medal, and only for the stadium(s) located in the city of 'Salvador'. Additionally, you want to filter the tests so that only those attended by the viewer with idDocument '24758693K' are displayed. Show results sorted by date, in descending order (most recent date first)
I have already advanced a lot, I only need the final part where it says that
Additionally, you want to filter the tests so that only those that the viewer with idDocument '24758693K has attended are shown.
This is what I have and things are going well
The problem is that when I put this AND person.iddocument= '24758693K'
, all the records disappear and I have verified through other queries and see the database that 3 records should appear... And this happens to me...
What is wrong with me? Some kind of relationship? Maybe the select is not done like this? I already tried many things but I can't.
I put the code of the query
select
person.firstname,person.lastname,competition.compdescription,venue.venue,
federation.federation,score.dateresult,venue.venue
from person,score,athlete,venue,federation,competition,city,assist
where person.idperson=athlete.idathlete
and athlete.idathletefed = federation.idfederation
and score.idathlete = athlete.idathlete
and score.idcompetition = competition.idcompetition
and competition.idvenue = venue.idvenue
and venue.idvenuecity=city.idcity
and assist.spectatorid=person.idperson
and person.gender = 'M'
and score.score in ('Gold')
and federation.federation in ('Union Cycliste Internationale')
and city.cityname in ('Salvador')
group by
person.firstname,
person.lastname,
competition.compdescription,
venue.venue,
federation.federation,
score.dateresult,
venue.venue;
The problem lies in understanding the statement, specifically:
As he tells you, he only wants to show tests that a spectator has attended. It does not say that this person is an athlete, and yet in your query you are forcing it to be, specifically with the line:
On the other hand, what you are being asked to do is filter the tests they have attended, not the athletes. Therefore, the solution that it returns should not be a list for the idDocument that they give you, but for each of the tests that that idDocument has attended as a public, the list of all the runners that have competed in them.
Since they don't allow you to do joins, a possible solution would be, instead of fetching the entire "Competition" table, fetching only the competitions that the viewer with idDocument='24758693K' has attended. Based on your query, it might be something like this:
I hope I've helped