CODEWITHSUNDEEP
mysqlview
anon
anon

Reputation:

Duplicate Column Name MySQL

I shouldn't have this error. here is the code

drop database if exists myDB;
CREATE DATABASE IF NOT EXISTS myDB;
USE myDB;

CREATE TABLE teacher(
tId int auto_increment primary key,
fname varchar(50),
lname varchar(50),
sId varchar(50)
);


CREATE TABLE student(
sId varchar(50) not null primary key,
fname varchar(50),
lname varchar(50)
);


ALTER TABLE teacher
    ADD foreign key (sId) references student(sId);
 
 insert into student values ('123', 'a', 'aa');
 insert into student values ('456', 'b', 'bb');
 insert into teacher (fname, lname, sId) values ('t', 'tt', '123');
 insert into teacher (fname, lname, sId) values ('x', 'xx', '456');
 
 
 
 create or replace view showTeachersAndStudents as
    select teacher.fname, teacher.lname, student.sId, student.fname, student.lname 
                from teacher join student on teacher.sId = student.sId;

The Error: Error Code: 1060. Duplicate column name 'fname'

Upvotes: 0

Views: 969

Answers (1)

McNets
McNets

Reputation: 10807

Your view try to return fname and lname two times.

Add alias to your view:

create or replace view showTeachersAndStudents as
    select teacher.fname fname_teacher, 
           teacher.lname lnamet_teacher, 
           student.sId, 
           student.fname fname_student, 
           student.lname lname_student
    from teacher 
    join student on teacher.sId = student.sId;

Upvotes: 4

Related Questions