Any Mysql guru who knows how to handle a custom group calculation scenario in 1 query?

Question

I hope any sense can be made from my explanation. I was able to create the query, however my query only works for Items related to Containers, AND only if no more than one Items are related. I really hope anybody can be of any assistance!

Consider the following objects:

• Container
• Person
• Item

I have one table where instances of all objects are stored. The table uses a self-referencing parent-child construction so it is db-technically possible to put a Container 'inside' a Person (just mentioning, this is not happening).

• Object_Instances
  • objectid
  • parentid
  • typeid
  • typespecification (same as containerid, personid or itemid => one of three is filled)
  • containerid
  • personid
  • itemid

I have two tables which can be used to link persons/items to containers:

• Container_Person

  • containerid
  • personid
  • amount
  • required (boolean)

• Container_Item

  • containerid
  • itemid
  • amount
  • required (boolean)

(there are also a person/*item* table)

Now for an instance of a container I would like to calculate a number between 0 and 1 which is based on the related Container_Person and Container_Item specification in the following way:

• if NO Container_Person/Container_Item are related to the Container => result = 1
• if there are related records they should be taken into account in the following manner:
  • if the Container DOES NOT contain (has a child record of) ALL of the related persons/items which are required => result 0
  • otherwise:
  • result = average based on:
  • (# child records vs amount in Container_xxx relation)
  • if there are more than 1 person or item related to the container then the 'weight' for the (#records vs amount) value should be the ratio between the related Container_xxx.amount values for that Container.

Here is my current 'solution':

This query only works for one related item to a container. It doesn't take persons into account in any way... If more than one item are related to the container then the query returns multiple records.

So my actual question is: How can I group/sum(calculate to 0 - 1 decimal) the results of the following query based on the _Person / _Item related amount/required specification?

SELECT
    Container.name,
    Item.name,
    (ifnull(Sum(Object_Instance.amount),0) / Container_Item.amount) as value
FROM
    Container
    Inner Join Object_Instances as Containers 
        ON Containers.typeid = 'container' 
        AND Container.containerid = Containers.typespecification
    Left Outer Join Container_Item ON Container_Item.containerid = Container.containerid
    Left Outer Join Item ON Item.itemid = Container_Item.itemid
    Left Outer Join Object_Instance as ContainerItems 
        ON Item.itemid = ContainerItems.typespecification 
        AND ContainerItems.typeid = 'item'
        AND ContainerItems.parentid = Containers.objectid
WHERE Containers.objectid = 1
GROUP BY 
    Container.name,
    Container_Item.amount,
    Item.name,
    Container.containerid