Thanks Jason - I actually had another table, nominations, which I can use. Basically each lodge can receive multiple votes / scores for any given category, for any given country. But would only have one nomination in any given category, for any given country. i.e. if it gets any votes in a category, for a country, it then gets a new nomination record.
And the idea is to use the that nominations record for judges to then flag any as finalists and rank the top 10.
I haven't been able to get the SQL for the query right though - presumably because of wrong JOINs and not being quite sure how to join on multiple criteria.
The tables are as above, plus:
nominations
NominationID (PK)
LodgeID (FK)
CategoriesFullID (FK) (--to categoriesFull.CategoryID)
SW_finalist
SW_rank
So if I have the following:
lodges
LodgeID, Lodge
798, Borana Lodge
scores
ScoreID, LodgeID, CategoryID, Score
1001, 798, 7, 3
1002, 798, 7, 6
1003, 798, 7, 9
categoriesFull
CategoryID
7
countries
CountryID
3
nominations
NominationID, LodgeID, CategoryID, SW_finalist, SW_rank
1234, 798, 7, Yes, 2
I'd like to be able to output
LodgeID, Lodge, CategoryID, CountryID, ScoreCount, AverageScore, NominationID, SW_finalist, SW_rank
798, Borana Lodge, 7, 3, 3, 6, 1234, Yes, 2
This is what I have before introducing the nominations table:
SELECT ScoreID, COUNT(ScoreID) as scoreCount, AVG(Score) as AverageScore, lodges.LodgeID, Lodge, Country, lodges.CountryID, CategoryID, SW_finalist, SW_rank FROM lodges INNER JOIN countries ON lodges.CountryID = countries.CountryID INNER JOIN nominations ON lodges.LodgeID=nominations.LodgeID INNER JOIN scores ON lodges.LodgeID = scores.LodgeID WHERE lodges.CountryID = 3 AND CategoryID='7' GROUP BY Lodge
I presume the join on nominations needs to be also be on categoriesFull.CategoryID, but when I try something like
SELECT ScoreID, COUNT(ScoreID) as scoreCount, AVG(Score) as AverageScore, lodges.LodgeID, Lodge, Country, lodges.CountryID, CategoryID, SW_finalist, SW_rank FROM lodges INNER JOIN countries ON lodges.CountryID = countries.CountryID LEFT OUTER JOIN nominations ON lodges.LodgeID=nominations.LodgeID AND nominations.CategoriesFullID = categoriesFull.CategoryID INNER JOIN scores ON lodges.LodgeID = scores.LodgeID WHERE lodges.CountryID = 3 AND CategoryID='7' GROUP BY Lodge
I get an error about categoriesFull.CategoryID being an unknown column.
I think all the tables have the IDs they need, but any help with the right joins to get it to work would be much appreciated.
