Hi all,
I have a basic multiple inheritance entity model mapped in 4 tables
so basically entities have key/value attributes and entities can have multiple parents as entities. If an entity's parent has an attribute then the entity will inherit it unless it has an attribute of the same key then it overrides the inherited attribute.
so in short I can run this query
Code:
select dr.entity_id, d.key, d.value, dr.depth
from data d
inner join data_relations dr on d.id=dr.data_id
where dr.entity_id in
(select a.id from
(SELECT
entity.id
FROM entity
left join data_relations on entity.id=data_relations.entity_id
left join data on data_relations.data_id=data.id
where (data.key='size' and data.value='A5') or (data.key='size' and data.value='A4')
) a
inner join
(SELECT
entity.id
FROM entity
left join data_relations on entity.id=data_relations.entity_id
left join data on data_relations.data_id=data.id
where (data.key='group' and data.value='p1')) b where b.id=a.id
)
and i get this output, which I expect it to be
entity_id key value depth
3 group p1 2
4 group p1 2
3 code 1111 1
4 code 1111 1
3 price £2 1
4 price £2 1
3 size A4 0
4 size A5 0
4 price £3 0
but (entity_id 4) has 2 price keys and I want it to only display the one price key per entity_id. The price key value to be used is the one with the lowest depth.
So the output should look like this
entity_id key value depth
3 group p1 2
4 group p1 2
3 code 1111 1
4 code 1111 1
3 price £2 1
4 price £2 1
3 size A4 0
4 size A5 0
4 price £3 0
I've tried using groups and Min but it isn't providing the right results
it should use price £3 for entity 4 but it ends up giving entity 4 £2 even though it reports the correct depth.
any help?
dBforums
>
filtering data
01-26-12, 20:03
Linear Mode
