sqldatabasems-access

QL query MS Access error "field could refer to more than one table"

Learning Microsoft Access and SQL I've normalized several fields into four (4) tables.

enter image description here

Why am I not able to run the SQL query below in MS Access?

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

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