I'm working on a EAV database implemented in MySQL so when I say entity, you can read that as table. Since it's a non-relational database I cannot provide any SQL for tables etc but I'm hoping to get the conceptual answer for a relational database and I will translate to EAV SQL myself. 

I'm building a mini stock market system. There is an "asset" entity that can have many "demand" and "offer" entities. The asset entity also may have many "deal" entites. Each deal entity has a "share\_price" attribute. Not all assets have demand, offer or deal entities. 

I want to return a list of offer and demand entities, grouped by asset i.e. if an asset has 2 offers and 3 demands only 1 result will show. This must be sorted by the highest share\_price of deals attached to assets of the demand or offer. Then, the highest share\_price for each demand or offer is sorted overall. If an asset has demands or offers but no deals, it will be returned with NULL for share\_price. 

So say the data is like this:

```
Asset 1 has 1 offer, 1 demand and 2 deals with share_price 7.50 and 12.00 
Asset 2 has 1 offer and 1 deal with share_price 8.00
Asset 3 has 3 offers and 3 demands and no deals
Asset 4 has no offers and no demand and 1 deal with share_price 13.00

```

I want the results:

```
Asset       share_price
Asset 1     12.00
Asset 2     8.00
Asset 3     null

```

Note: Asset 4 is not in the result set because it has no offers or demands. 

I know this is a complex one with I really dont want to have to go to database more than once or do any array re-ordering in PHP. Any help greatly appreciated. 

Some users want to see SQL I have. Here it is but this won't make too much sense as its a specialised EAV Database. 

```
SELECT DISTINCT data.asset_guid, r.guid_two, data.share_price FROM (

    select rr.guid_one as asset_guid, max(msv.string) as share_price from market_entities ee

    join market_entity_relationships rr on ee.guid = rr.guid_two
    JOIN market_metadata as mt on ee.guid = mt.entity_guid
            JOIN market_metastrings as msn on mt.name_id = msn.id
            JOIN market_metastrings as msv on mt.value_id = msv.id

    where subtype = 6 and msn.string = 'share_price' and rr.relationship = 'asset_deal'
    group by                    
        rr.guid_one 
    ) data 

   left outer JOIN market_entities e on e.guid = data.asset_guid  
   left outer JOIN market_entity_relationships r on r.guid_one = e.guid  
   WHERE  r.relationship = 'trade_share' 
   GROUP BY data.asset_guid

```