Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

MySql: Can I dynamically assign a column alias.

  • 13-08-2010 11:48AM
    #1
    Registered Users, Registered Users 2 Posts: 302 ✭✭


    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.


Comments

  • Registered Users, Registered Users 2 Posts: 23,202 ✭✭✭✭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.


  • Registered Users, Registered Users 2 Posts: 302 ✭✭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.


  • Registered Users, Registered Users 2 Posts: 23,202 ✭✭✭✭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.


Advertisement