I currently have the following tables:
lodges
LodgeID (PK)
Lodge
etc
scores
ScoreID (PK)
Score
CategoryID (FK)
LodgeID (FK)
(also used to calculate ScoreCount and AverageScore)
categoriesFull
CategoryID (PK)
Category
countries
CountryID (PK)
Country
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 had 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
Which seemed to work to output:
LodgeID, Lodge, CategoryID, CountryID, ScoreCount, AverageScore
798, Borana Lodge, 7, 3, 3, 6
I just need to tag on those three fields from the nominations table. 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.
Thank you.