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
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