Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

1 Answer 1

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

Melbourne.png

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

SELECT 
    Instance.Description, 
    City.CityName, 
    Accommodation.CategoryType
FROM 
    (
        City 
        INNER JOIN 
        Instance 
            ON City.City_ID = Instance.City_ID
    ) 
    INNER JOIN 
    Accommodation 
        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
add comment

Your Answer

 
discard

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.