Learning how to join tables but receiving an error that says "Not unique table"

Question

I want to join two tables together which are the run table and the restaurant table. From the run table I need the run_id and the restaurant_id and from the restaurant table, I need the created_date so in the end, I have a table that contains the created_date of each restaurant. Both the run and restaurant_history tables have run_id's so that's how I know I can join them. I came up with something like:

SELECT run_id, restaurant_id, created_date FROM restaurant_history, run
JOIN run ON restaurant_history.run_id = run.run_id;

But that gave me an error. Any help would be appreciated :)

(I'm fairly new to sql)

Answer 1

You are mixing both implicit joins and explicit joins. The implicit join syntax (listing the tables in the FROM clause) was deprecated over 25 years ago.

As a simple rule, never use commas in the FROM clause:

SELECT R.run_id, restaurant_id, created_date 
FROM   restaurant_history H
JOIN   run                R ON H.run_id = R.run_id;

As for why it gave you that error, error is two-fold. Let's look at what you had written:

SELECT run_id, restaurant_id, created_date 
FROM   restaurant_history, run
JOIN   run ON restaurant_history.run_id = run.run_id;

The query you had before was the equivalent of the following:

SELECT     run_id, restaurant_id, created_date 
FROM       restaurant_history
CROSS JOIN run
INNER JOIN run ON restaurant_history.run_id = run.run_id;

The reason for the error was because you had the table run listed twice in the query with no aliases to discern between the two. The ON clause referenced the run table, but it didn't know which one you meant.

Additionally, you're unintentionally creating a CROSS JOIN between restaurant_history and run - something I'm sure you don't want.

But just removing the second table from the FROM clause will still give you an error about an ambiguous column (run_id) in your SELECT statement. This column exists in both tables (as we can see from the JOIN), and without explicitly telling it which table to select from, it doesn't know how to handle the column and will throw an error.

To fix this, you will also need to alias the tables (the H and R aliases I've put in the solution).

---

For more information on different JOIN types, see this question:
What is the difference between "INNER JOIN" and "OUTER JOIN"?

For more information on Explicit vs Implicit JOINs, see here:
Bad habits to kick : using old-style JOINs

Answer 2

SELECT run_id, restaurant_id, created_date 
FROM restaurant_history H 
INNER JOIN run R ON H.run_id = R.run_id

Answer 3

Try this query

SELECT run_id, restaurant_id, created_date 
FROM restaurant_history  
INNER JOIN run ON restaurant_history.restaurant_id= run.run_id;