Doing calculations in linked tables.

[Ragnar Brynjulfsson]

Hi,

I have two tables, one with assets and one with tasks. Each asset can have n number of tasks assigned to it and each task has a status number/percent. What I want to do is to create a view that contains the average status of all tasks assigned to an asset, and join that with the asset list.

Maybe this exlpains it better. Here are the tables I have, and the resulting view.

Asset table:
 id      name
  1      asset1
  2      asset2

Task table:
 id    name    status   linked_asset_id
  1    task1     20       1
  2    task2     30       1
  3    task       5       2

Resulting view (joining the asset and view should be)
 id    name   status
  1    asset1   25
  2    asset2    5

Hope this makes at least some sense.

How would I go about this in MySql?

Cheers,

Ragnar