How to create a view/table in my context in mysql ?

Question

Hello,

I want to create a view/table in mysql. I have two tables now:

create table teacher(
id int(10) not null auto_increment, 
name varchar(50), 
primary key(id)
);
create table student(
id int(10) not null auto_increment, 
name varchar(50), 
teacher_id int(10), 
primary key(id), 
foreign key(teacher_id) references teacher(id) on delete cascade);

Now, I want to get teacher.name and student.name in the same column, and make teacher.teacher_id as parent_id, student.id as id.

The new table should be the structure:

table(parent_id, id, name);  

Edit:

The value of "name" column is either teacher's name or student's name.

example:

parent_id     id             name
  0            1           teacher1
  1            2           student1
  1            3           student2
  0            4           teacher2
  4            5           student3

How to do that? Thanks in advance ;)

Edit: is there someone who knows how to do that? Wondering...

Answer 1

I'm not sure I understand what you mean by getting teacher.name and student.name in the same column. I've assumed you mean you want to join them together as a string, eg "John Smith, Michael Jones".

CREATE VIEW teacherstudent AS
SELECT teacher.id AS parent_id, student.id AS child_id, CONCAT(teacher.name,', ',student.name) AS name
FROM teacher, student
WHERE teacher.id = student.teacher_id;

You can now just do a select * from teacherstudent;