mySQL query syntax - multiple tables and joins

Databases
1

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.

2

As far as I know, you can’t use the AND operator in a JOIN condition, so it’s the second part of this line that I think is causing the problem:

LEFT OUTER JOIN nominations ON lodges.LodgeID=nominations.LodgeID AND nominations.CategoriesFullID = categoriesFull.CategoryID

As you’re already joining the nominations table on the LodgeID column, all you should need to do is add the three extra columns you want to the column list and the query should work.