CODEWITHSUNDEEP
mysql
wrichards0
wrichards0

Reputation: 187

SQL column name ambiguous

I am having trouble with a new IMDB like system I'm building. My specific issue is that when I run:

CREATE VIEW `directors` AS 
SELECT 
`stars`.`id` AS `movie_id`, 
`stars`.`title`,`stars`.`rating`, 
`stars`.`storyline`, 
`stars`.`star`, 
`people_list`.`name` AS `director` 
FROM `stars` 
INNER JOIN `stars` 
ON `movie_directors`.`movie` = `stars`.`id` 
INNER JOIN `people_list` 
ON `movie_directors`.`director` = `people_list`.`id` 
WHERE `movie_directors`.`enabled` = 1;

I get the following error:

#1052 - Column 'stars.id' in field list is ambiguous

All of the questions I've found on here seem to relate to when you don't prefix the column name with a table name or, in this case, a view name since I'm writing a view to build off another view

Upvotes: 0

Views: 246

Answers (4)

gr1zzly be4r
gr1zzly be4r

Reputation: 2152

You are selecting from stars and then INNER JOINing on stars:

SELECT ... FROM stars INNER JOIN stars

I think that you probably want to join with movie_directors based on your query.

Upvotes: 2

Zach Dennis
Zach Dennis

Reputation: 1784

It looks to be ambiguous because you have two references to the stars table. Your FROM clause and your first INNER JOIN.

It looks like you are intending to join on movie_directors instead of INNER JOIN stars clause. E.g. 

CREATE VIEW `directors` AS SELECT 
 `stars`.`id` AS `movie_id`, 
 `stars`.`title`,`stars`.`rating`, `stars`.`storyline`, `stars`.`star`, `people_list`.`name` AS `director`
FROM `stars` 
INNER JOIN `movie_directors` ON `movie_directors`.`movie` = `stars`.`id` 
INNER JOIN `people_list` ON `movie_directors`.`director` = `people_list`.`id` WHERE `movie_directors`.`enabled` = 1;

Hope this helps!

Upvotes: 0

Ed Mozley
Ed Mozley

Reputation: 3519

I think it's because you are not aliasing correctly.

It should be something like

select ... from stars inner join stars as anothername

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You are using a self join (starts table is used two time) in this case you need an alias for refer the proper table instance

  CREATE VIEW `directors` AS 
  SELECT 
      `stars`.`id` AS `movie_id`
    , `stars`.`title`
    ,`stars`.`rating`
    , `stars`.`storyline`
    , `stars`.`star`
    , `people_list`.`name` AS `director` 
  FROM `stars` 
  INNER JOIN `stars` as s2 ON `movie_directors`.`movie` = s2.`id` 
  INNER JOIN `people_list` ON `movie_directors`.`director` = `people_list`.`id` 
  WHERE `movie_directors`.`enabled` = 1;

Upvotes: 0

Related Questions