Mysql: Create a view with multiple self joins without duplicates in result

Question

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:

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!

Answer 1

Try using conditional aggregation instead:

select eav.entity_id,
       max(case when entity_attributes_id = 2 then eav.value end) as company_id,
       max(case when entity_attributes_id = 1 then eav.value end) as entity_name,
       max(case when entity_attributes_id = 3 then eav.value end) as company_name,
       . . .
from entities_attributes_values eav
group by eav.entity_id;

This will make it easy to add new attributes to the view. Also, don't use single quotes to delimit column names. Single quotes should only be used for date and time constants.