Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have three tables:

Table City has Two columns:

City_ID, CityName <City_ID is Primary Key>

Table Accommodation has Two Columns:

Accommodation_ID, CategoryType <Accommodaton_ID is Primary Key>

Table Instance has four columns:

Instance_ID, Description, City_ID, Accommodation_ID <Instance_ID is Primary Key; City_ID is Foreign Key, Accommodation_ID is Foreign Key>

I am trying to run an SQL query to "Get all Description as long as "CityName=Melbourne" and "CategoryType=Hotel".

My query returns multiple rows of same values: See my sql code below:

SELECT C.Description, A.CityName, B.CategoryType
FROM Instance AS C, City as A, Accommodation as B
where A.City_ID = (select City_ID from City where CityName = 'Melbourne') AND A.City_ID = C.City_ID AND B.CategoryType = 'Hotel';

I am using MS Access Please what am I doing wrongly.. Thank you

share|improve this question
up vote 0 down vote accepted

Your query is missing AND C.Accommodation_ID = B.Accommodation_ID

If you are just getting started with SQL then you might consider building simple queries like this in the Access query designer and then switch to SQL view to see what the query designer has generated. In your case we have


and Access generates the following SQL (which I've indented for clarity)

        INNER JOIN 
            ON City.City_ID = Instance.City_ID
        ON Instance.Accommodation_ID = Accommodation.Accommodation_ID
WHERE (((City.CityName)="Melbourne") AND ((Accommodation.CategoryType)="Hotel"));

Note that the query uses INNER JOIN syntax, which is generally preferred over the older method of putting the join conditions in the WHERE clause.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.