I have 2 Tables in MySQL
Directors Table
| director_id (PK, FK) | director_name |
|---|---|
| (INT) | VARCHAR(100) |
Movies Table
| movie_id (PK) | director_id (FK) | movie_name | released_date |
|---|---|---|---|
| INT | INT | VARCHAR(100) | DATE |
I am trying to query over the two tables to return the date of a directors first movie that they directed. Here is my code below:
WITH CTE AS (
SELECT director_id, movie_id, movie_name, released_date,
row_number() OVER (partition BY movie_id ORDER BY released_date DESC) rownumber
FROM Movies )
SELECT Directors.director_name, director_id as 'ID', movie_id, movie_name, released_date
FROM CTE
LEFT JOIN Directors ON CTE.director_id = Directors.director_id
WHERE CTE.rownumber = 1;
But I get the Error: ERROR 1052 (23000): Column 'CTE.director_id' in field list is ambiguous
I believe this is the correct approach to answering the question?
