Help with SQL Join Query

Question

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 Answer

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded