Results 1 to 3 of 3

Thread: filtering data

  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Unanswered: filtering data

    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?

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Have you tried using SELECT DISTINCT?

    Ignore this will not solve this problem.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jan 2012
    Posts
    2
    Yeah distinct wouldn't work

    I know what the issue is, I just don't know if i'm solving my problem correctly.

    it's because when I group by entity and key it is grouping on those values and not taking in to consideration the aggregate min value

    driving me mad!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •