Hi,
This query works now, but it needs to be more specific for the customer. I also looked to see if I can adjust the data in the (PHP) array, but I prefer to pull it out of the query so that sorting continues to work properly. I've been stuck on this part for a while now. SQL statements are not my specialty.
Now it shows the total points well, but what I want to achieve is the following:
- It must count the points of the national show (type 1) first, since an animal participates in a national show a maximum of 1 time per year.
- Then it must select the 3 highest points and then stop counting (LIMIT).
So when an animal has not yet participated in a national show, it takes the 3 highest scores and if one has participated, it still takes the 3 highest scores, but the national one must be above that.
(show_participants.participant_category = "1" is now static, but this is for testing. There are 10 categories)
Hope it's clear, thanks a lot in advance.
SELECT Sum(participant_match_points) AS participant_total_points,
shows.show_type,
show_types.type_name,
animals.born,
animals.registrationnumber,
owners.ownernumber
FROM show_participants
INNER JOIN animals
ON animals.registrationnumber =
show_participants.participant_animal
INNER JOIN show_categories
ON show_participants.participant_category =
show_categories.category_id
INNER JOIN owners
ON animals.registrationnumber = owners.registrationnumber
INNER JOIN shows
ON show_participants.participant_show = shows.show_id
INNER JOIN show_types
ON show_types.type_id = shows.show_type
WHERE show_participants.participant_category = "1"
GROUP BY show_participants.participant_animal
ORDER BY participant_total_points DESC
