1

Just to clarify i can't change the tables structure, so please leave out the "you should change your tables to this and that" answers, thank you.

So i have a table entities_attributes_values where an entity has a lot of attributes and the value of that attribute, basically imagine 3 fields:

  • entity_id
  • entity_attributes_id
  • value

Because every entities attribute and its value is on row getting more values is not so easy i was thinking of multiple self joins, and because this query will be very common i created a view, which is built with this query:

SELECT `L1`.`entity_id`,
       `L1`.`value` as 'company_id',
       `L2`.`value` as 'entity_name',
       `P`.`value` as 'person_name',
       `L4`.`value` as 'establishment_id',
       `L5`.`value` as 'department_id'
FROM `entities_attributes_values` `L1`
LEFT JOIN `entities_attributes_values` `L2` ON `L1`.`entity_id` = `L2`.`entity_id` AND `L2`.`entity_attributes_id` = 1
LEFT JOIN `entities_attributes_values` `L3` ON `L1`.`entity_id` = `L3`.`entity_id` AND `L3`.`entity_attributes_id` = 3
LEFT JOIN `persons_attributes_values` `P` ON `L3`.`value` = `P`.`core_persons_id` AND `P`.`core_persons_attributes_id` = 4
LEFT JOIN `entities_attributes_values` `L4` ON `L1`.`entity_id` = `L4`.`entity_id` AND `L4`.`entity_attributes_id` = 12
LEFT JOIN `entities_attributes_values` `L5` ON `L1`.`entity_id` = `L5`.`entity_id` AND `L5`.`entity_attributes_id` = 13
WHERE `L1`.`entity_attributes_id` = 2

So this works but i have one problem i get "duplicate" values and its not really duplicate but the point is that in my view i want every entity to be only one row with all its attributes values but instead i get this:

enter image description here

So as you can see the first three result are not good for me, i only need the fourth one, where i have all my data about one entity.

Thank you in advance for any help!

0

Inner Join:

An inner join produces a result set that is limited to the rows where there is a match in both tables for what we're looking for. If you don't know which kind of join you need, this will usually be your best bet.

Left Outer Join:

Add to Where

... and establishment_id is not null and department_id is not null   

And sometimes just to make it complete i do also

... and establishment_id<>'' and department_id<>''
| improve this answer | |
  • Sorry, but i'm not so experienced in mysql, so i dont exactly understand your answer, could you please modify it so that its the full query? Thank you so much – Mr. Sam Jun 24 '15 at 10:35
  • I added youre suggetions but the results is the same :( – Mr. Sam Jun 24 '15 at 10:40
  • (check if maybe typo: L3 -> L1 ?) Are you sure it's "LEFT JOIN persons_attributes_values P ON L3.value = ..." and not "LEFT JOIN persons_attributes_values P ON L1.value =" ??? – aldwinaldwin Jun 24 '15 at 11:07
0

Since you have a key-value store instead of a schema, it becomes tricky to assume anything about the result (you should really ...:-). That said you can try applying an aggregate function to your columns to get rid of the nulls:

SELECT `L1`.`entity_id`,
       MAX(`L1`.`value`) as 'company_id',
       MAX(`L2`.`value`) as 'entity_name',
       MAX(`P`.`value`) as 'person_name',
       MAX(`L4`.`value`) as 'establishment_id',
       MAX(`L5`.`value`) as 'department_id'
FROM `entities_attributes_values` `L1`
LEFT JOIN `entities_attributes_values` `L2` 
    ON `L1`.`entity_id` = `L2`.`entity_id` 
   AND `L2`.`entity_attributes_id` = 1
LEFT JOIN `entities_attributes_values` `L3` 
    ON `L1`.`entity_id` = `L3`.`entity_id` 
   AND `L3`.`entity_attributes_id` = 3
LEFT JOIN `persons_attributes_values` `P` 
    ON `L3`.`value` = `P`.`core_persons_id` -- is this correct?
   AND `P`.`core_persons_attributes_id` = 4
LEFT JOIN `entities_attributes_values` `L4` 
    ON `L1`.`entity_id` = `L4`.`entity_id` 
   AND `L4`.`entity_attributes_id` = 12
LEFT JOIN `entities_attributes_values` `L5` 
    ON `L1`.`entity_id` = `L5`.`entity_id` 
   AND `L5`.`entity_attributes_id` = 13
WHERE `L1`.`entity_attributes_id` = 2
GROUP BY `L1`.`entity_id`

I assume entity_id, entity_attributes_id is unique

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.