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
LodgeID (PK)
Lodge
etc
Table : nominations
NominationID (PK)
Category
LodgeID
Year
So I'm trying to use that structure to replicate this page:
http://www.safariawards.com/nominees12/
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:
http://www.safariawards.com/nominees12/southernafrica.php
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.
