Help with SQL Join
I'm just having a bit of troubel getting a Join query correct - I thought it was an Inner Join, but am not getting the results I'd expect.
My table structure is:
Table : lodges
Table : nominations
So I'm trying to use that structure to replicate this page:
ie a list of lodges for each category they are nominated in.
The query I've tried looks like this:
SELECT nominations.LodgeID, lodges.Lodge, nominations.NominationID, nominations.Category FROM lodges INNER JOIN nominations ON lodges.LodgeID=nominations.NominationID WHERE category='Best Safari Property in Southern Africa' ORDER BY Lodge
But is producing this:
Its the right number of results, but not the right list of lodges - for example British Airwways isn't LodgeID 786
If anyone could help out with the right SQL for this that would be much appreciated.