Ms-access – Query Multiple tables in MS Access

ms access

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

Best Answer

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.

Related Question