SQL SELECT combined with JOINS and CONCATS produces multiplied entrys of the same data in one selected field

Question

The situation at hand:

• Table fotos_images => 1 line of data

  basic image informations

• Table fotos_keywords => 1 line of data

  One Keyword 'Sonne'

• Table fotos_locations => 3 lines of data

  Line with 'Berlin', next line 'Treptow-Köpenick', next line 'Deutschland'

• Table fotos_locations_images_idx => 3 lines of data

  Three pairs of id combinations from 'fotos_images' and 'fotos_locations'

• Table fotos_keywords_images_idx => 1 line of data

  one pair of id combinations from 'fotos_keywords' and 'fotos_images'

There will be more in there - hundreds and more, now it's only one single image file that was saved in the database. Images will have 0 to n locations and 0 to n keywords.

My query to get all data of one image in one nice line to work with further in this part ot the program:

SELECT i.id
     , i.name
     , i.filename
     , i.iso
     , i.aperture
     , i.camera
     , i.exposure
     , i.copyright
     , i.timestamp
     , i.text
     , GROUP_CONCAT(k.keyword SEPARATOR ', ') keyword
     , GROUP_CONCAT(l.location SEPARATOR ', ') location 
  FROM fotos_images i
  LEFT 
  JOIN fotos_keywords_images_idx ki
    ON i.id = ki.id_image 
  LEFT 
  JOIN fotos_keywords k
    ON ki.id_keyword = k.id 
  LEFT 
  JOIN fotos_locations_images_idx  li
    ON i.id = li.id_image 
  LEFT 
  JOIN fotos_locations l
    ON li.id_location = l.id 
 GROUP 
    BY i.id
     , i.name
     , i.filename
     , i.iso
     , i.aperture
     , i.camera
     , i.exposure
     , i.copyright
     , i.timestamp
     , i.text;

What I get from the query:

id         197
name       Abendsonne   
filename   caa3005cce1791f3e27e8ce4cf11ee59.jpg 
iso        100  
aperture   f/5.6
camera     Canon EOS 60D
exposure   1/250
copyright  Hans Otto
timestamp  2014-01-07 01:08:02
text
keyword    Sonne, Sonne, Sonne
location   Berlin, Treptow-Köpenick, Deutschland

What I expect (or want to) get from the query:

id         197
name       Abendsonne   
filename   caa3005cce1791f3e27e8ce4cf11ee59.jpg 
iso        100  
aperture   f/5.6
camera     Canon EOS 60D
exposure   1/250
copyright  Hans Otto
timestamp  2014-01-07 01:08:02
text
keyword    Sonne
location   Berlin, Treptow-Köpenick, Deutschland

Look at 'keyword' because THIS image has only one keyword attached to it - others may have more or zero. But why is it multiplied and how to get rid of that math trick? I get it that it has something to do with the locations but I don't know how to solve that puzzle.

Any hint or solution really appreciated!

Answer 1

It would work correctly if you just added DISTINCT in front of k.keyword SEPARATOR ', ':

...
, GROUP_CONCAT(DISTINCT k.keyword SEPARATOR ', ') keyword
...