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

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:

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

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:

• sql
• database
• ms-access

• 4 Bad habits to kick: using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 30 years ago!) and its use is discouraged – marc_s Commented Jan 20 at 19:14
• 1 as to the specific error both tables contain cust_ID. So you have to specify which cust_ID you want to use rentals.Cust_ID or customer.cust_ID but yes, use INNER, LEFT, RIGHT joins when joining data don't use a , in the from clause anymore. – xQbert Commented Jan 20 at 19:14
• 1 Second step: use meaningful table aliases like FROM Customer c INNER JOIN Rentals R ON and then start using those aliases to define which column (from which table) you want – marc_s Commented Jan 20 at 19:15
• 1 And your where statement looks wrong as well, as you probably want CUST_ID from both tables, again specifying which table as per xQbert's comment. You don't seem to have a ID field in either table. – Dijkgraaf Commented Jan 20 at 19:16
• Put it all together and you get... SELECT CUST_FNAME, CUST_LNAME FROM RENTALS R INNER JOIN CUSTOMERS C ON R.CUST_ID = C.CUST_ID WHERE MEDIA_TITLE = "The Love Boat"; Assuming you want all customers who have rented "the Love Boat" and not just all customers and call out those who have rented the movie in question. If you used the graphical query builder in Access, it wouldn't use the comma in the from – xQbert Commented Jan 20 at 19:17

1 Answer 1

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