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

duplicationjoin;MySQLview

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!

Best Answer

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<>''
Related Question