devxlogo

Selecting Data from Views with Aliases

Selecting Data from Views with Aliases

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.

devx-admin

Share the Post: