Solved

mysql computed field

Posted on 2004-11-01
904 Views
Last Modified: 2012-06-27
Hello,

I'm not sure if i can do this, but is it possible to make a computed field in mysql, like say for instance I want to have a field called fullname which will automaticaly create a users full name from the field firstname and lastname. Also, if this is possible is it possible to have computed fields between tables, like say for instance i have a table for a customers quotes, with a dollar amount, and then i have another table called customerTotals which gives the customers total dollar amount of quotes for the year, is it possible to have this total dollar amount of quotes for the year field calculated even tho they are in different tables?

Thanks!

- Loren
0
Question by:LTY83
    4 Comments
     
    LVL 3

    Expert Comment

    by:alskdj80
    i dont think this is possible... mysql is just storage, i dont think it has these features

    if im wrong, someone plz correct me... this sounds rather interesting if it does exist
    0
     
    LVL 14

    Accepted Solution

    by:
    for the first question, yes :

    SELECT CONCAT_WS(' ', firstname, lastname) AS fullname
    FROM customers

    for the second question probably yes but i don't have your table structure. it should be something like this :

    SELECT CONCAT_WS(' ', c.firstname, c.lastname) AS fullname, ct.amout, SUM(cq.quotes) AS yearquotes
    FROM customers AS c INNER JOIN customertotal AS ct ON c.customerid = ct.customerid
                                     INNER JOIN customerquotes AS cq ON (c.customerid = cq.customerid AND ct.year = cq.year)
    GROUP BY cq.customerid, cq.year
     
    0
     
    LVL 4

    Author Comment

    by:LTY83
    but can this be done in the table automatically, IE: data is entered thru asp/sql but in the table the full name field generates itself?
    0
     
    LVL 14

    Expert Comment

    by:psadac
    yes, you can update a field with a computed expression :

    UPDATE customer SET fullname = CONCAT_WS(' ', firstname, lastname)
    WHERE fullname IS NULL
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    Creating and Managing Databases with phpMyAdmin in cPanel.
    This video Micro Tutorial is the first in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 (http://www.experts-exchange.com/articles/17490/). But the ability to create custom scanning profiles al…
    Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

    875 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now