MySql: Can I dynamically assign a column alias.

BlueSpud

Hi,
I am trying top create a view where I want a particular column name to be based on database content rather than a literal. I have donre an example of what I want to do below, but this is not allowed. Can enyone tell me if this can be done, and if so, how do I do it. Thanks in advance.

Occupation
===========
ID NAME
-- ----
01 Builder
02 Teacher
User
=======
ID NAME OCCUPATION_ID
--

---

---

01 Fred 02
02 Jim 02
03 Tom 01
04 Pat 02
create view MyView
as
select u.id, u.name, o.name as (select name from occupation where id = 02)
from user u, occupation o
where u.occupation_id = o.id
and u.occupation_id = 02;
to give me a view MyView as follows:
ID NAME TEACHER
-- ----

---

Here, the column name TEACHER is based on database content, not a literal.

Tom Dunne

BlueSpud wrote: »

create view MyView
as
select u.id, u.name, o.name as (select name from occupation where id = 02)
from user u, occupation o
where u.occupation_id = o.id
and u.occupation_id = 02;
to give me a view MyView as follows:
ID NAME TEACHER
-- ----

---

Here, the column name TEACHER is based on database content, not a literal.

Try leaving out the bit in bold.

BlueSpud

Leaving out the bit in bold would result in column names ID, NAME and NAME (ambiguity problem), which is not what I want. I want the name of the 3rd column to be based on the database content, in this case TEACHER. I will typically not know the value in the database so I cant simply code in 'TEACHER', as it could be PLUMBER.

Tom Dunne

BlueSpud wrote: »

Leaving out the bit in bold would result in column names ID, NAME and NAME (ambiguity problem), which is not what I want. I want the name of the 3rd column to be based on the database content, in this case TEACHER. I will typically not know the value in the database so I cant simply code in 'TEACHER', as it could be PLUMBER.

Ah, ok. Now I see what you want to do.

I don't have a database in front of me at the moment, but can MySQL do dynamic SQL?

As in, dynamically create a string 'CREATE VIEW XXXX as SELECT XXX' and then actually execute that string as an SQL command.

That's one way I could think of doing it.