Error Code: 1060 when creating view

Question

I'm trying to create a view but I get duplicate column name 'id' error. It works on it's own but as soon as i create a view it doesn't work.

create view question1_responders as select * from survey_responders join survey_responses on survey_responses.survey_responder_id = survey_responders.id where survey_question_id = 1 order by survey_responders.id

Answer 1

Change your CREATE VIEW statement to be like below by qualifying each column name. The error is mostly because of the select * part. Either specify specific columns you want to fetch (OR) specify table_name.*

create view question1_responders as
select sr.* from survey_responders sr
    join survey_responses sres
    on sres.survey_responder_id = sr.id
    where sr.survey_question_id = 1
    order by sr.id