Search code examples
sqldatabasems-accessdatabase-normalization

Normalization of Video Rental Store Database

So I'm learning Microsoft Access and SQL. I've normalized several fields into the following four (4) tables (as shown in Relationships picture) and assuming that they are correct I'm not able to run the SQL query below in MS Access. Can someone please explain what I'm doing wrong in my example please ?

SELECT CUST_FNAME, CUST_LNAME
FROM RENTALS, CUSTOMERS
WHERE ID = CUST_ID AND MEDIA_TITLE = "The Love Boat";

enter image description here

I get the following error:

enter image description here

Solution

  • There is no ID column. You have two tables that both have CUST_ID columns, and therefore to avoid ambiguity you must prefix the column name with the table name.

    Even better, when you're in that situation you should give your tables a mnemonic alias and for the love of God don't use that janky ancient FROM A,B syntax for JOINs.

    SELECT c.CUST_FNAME, c.CUST_LNAME
FROM RENTALS r
INNER JOIN CUSTOMERS c ON c.CUST_ID = r.CUST_ID
WHERE r.MEDIA_TITLE = 'The Love Boat';

    The other thing I'd do is add an id column to the Media table, since there can certainly be a number of items sharing the same title