Help with SQL Join Query

[johngordon12]

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.

[bregent · Accepted solution]

You're joining on the wrong column. Try this:

SELECT nominations.LodgeID, lodges.Lodge, nominations.NominationID, nominations.Category FROM lodges INNER JOIN nominations ON lodges.LodgeID=nominations.LodgeID WHERE category='Best Safari Property in Southern Africa' ORDER BY Lodge

[johngordon12]

Thank you - should have spotted that, but didn't see it for looking!