Altering the View in mySQL to add another column belonging to another table

Question

I have a database named dev and a view named employees_view (primary key - idEmployee). Now I want to add another (existing) column named Employee_Type (VARCHAR 45) belonging to another (existing) table Ref_Employee_Categories (primary key- idEmployee_Category) to my view - employees_view.

I am not too good with databases so I need to know, how can I do this. All I know about altering views is : ALTER VIEW dev.employee_view ADD dev.Ref_Employee_Categories.Employee_Type varchar (45)

Is what I wrote above correct? If not what is the proper syntax of doing that?

Answer 1

noup, it's not correct, a view it's (like) just a easy way to make a query , for example.

if you have something like this:

select * from table_1 as t1 join table_2 as t2 on t1.id=t2.id join table_3 ....... join table_100 on t100.id=t99.id

you can resume all that query and make:

CREATE VIEW big_query AS select * from table_1 as t1 join table_2 as t2 on t1.id=t2.id join table_3 ....... join table_100 on t100.id=t99.id;

If you now make select * from big_query you will execute the 100's joins.

If you want to change your View and add another column, you will have to write the query manually and after that create a VIEW

you can find more info here: http://dev.mysql.com/doc/refman/5.0/en/create-view.html