Question:
If I have created a view from two tables that happen to have a similar column name (e.g., name) :
CREATE VIEW team AS SELECT p.name, p.age, t.name, t.city
FROM PLAYER p, TEAM t where ...
How do I then select this information from the view? Do I have to use the same aliases?
Answer:
The aliases of “p” and “t” are only for the table names. To create a view that includes columns of the same name, you need to create an alias for the column, like this:
CREATE VIEW team AS SELECT p.name AS PName, p.age AS PAge,
t.name AS TName, t.city AS TCity FROM PLAYER p, TEAM t
where ....
Then you access the columns by their aliases, like this:
SELECT PName, PAge, TNameFROM Team
If you try to create a view with columns of the same name, it will return an error.
