MySQL column definition

Question

Is there a way, using MySQL 5.0, to define a column in a table that is to be calculated whenever a select is executed on that particular row? For example say I have two tables, A and B:

A:
    ID
    COMPUTED_FIELD = SUM(SELECT B.SOME_VALUE FROM B WHERE B.A_ID = ID)
B:
    ID
    A_ID
    SOME_VALUE

In the example, when I run a select on A for a particular ID, I want it to return the sum of all values in Table B for that particular value of A.ID. I know how to do this using multiple separate queries and doing a group by A_ID, but I'm trying to streamline the process a little.

Answer 1

Yes. You cannot do that inside a table, but you can do it in a view.

CREATE VIEW A 
AS 
SELECT SUM(B.SOME_VALUE) AS COMPUTED_FIELD 
FROM B 
WHERE B.A_ID = 'id';

Obviously id needs to be whatever you are searching for.

You don't need table A in this case.

Answer 2

Tables cannot contain calculated values. Try using views. The manual entry has full details. Your end result will come out looking something like: CREATE VIEW A AS SELECT SUM('SOME_VALUE'), B.A_ID FROM B; (Not tested)

That said, I'm not sure how important it is for you to have an independent, unique ID for table A -- this isn't possible unless you add another table C to hold the foreign keys referenced by B.A_ID, and use table C as a reference in creating your view.

Answer 3

As txwikinger suggests, the best way to do this is set up A as a view, not a table. Views are, for all intents and purposes, a streamlined, reusable query. They're generally used when a)a common query has a computed column, or b)to abstract away complex joins that are often used.

To expand on the previous answer, in order to be able to query A for any ID, try this view:

CREATE VIEW A
AS
SELECT B.A_ID AS ID, SUM(B.SOME_VALUE) AS COMPUTED_FIELD
FROM B
GROUP BY B.A_ID;

Then you can select into it normally, for example:

SELECT A.ID, A.COMPUTED_FIELD
FROM A
WHERE A.ID IN (10, 30);

or

SELECT A.ID, A.COMPUTED_FIELD
FROM A
WHERE COMPUTED_FIELD < 5;