0

I could use help with the database query I am trying to pull off. I have a table that has a word or phrase, a product id, a language id, and a version id. The version_id is a comma separated column, since the same word can be used in multiple versions and I tried to avoid adding another row with same data except for a different version.

The table currently looks like this enter image description here

I am trying to get the COUNT of word per product, version, and language. The problem I am getting is seperating the version_id since it is a comma seperated column. Is there a way to get the count for all words in version 1, and version 2, separately?

I currently have this query, which works, but it does not separate by versions in a comma separated column.

SELECT COUNT( translation ) AS translations, fdp.name AS product, fdv.name AS version, fdl.name AS language, fdl.position 
FROM `fdnfortidictionary_dictionary` AS `fdd`  
LEFT JOIN `fdnfortidictionary_product` AS `fdp` ON fdd.product_id = fdp.id  
LEFT JOIN `fdnfortidictionary_version` AS `fdv` ON fdd.version_id = fdv.id  
LEFT JOIN `fdnfortidictionary_language` AS `fdl` ON fdd.language_id = fdl.id WHERE fdp.enable = 1 
  AND fdd.product_id IN (1,2) 
  AND CONCAT( ",", fdd.version_id, "," ) REGEXP ",(1|2|3|4|5)," 
GROUP BY `language`,`version`,`product`,`position` 
ORDER BY fdl.position ASC

The Result I am looking for is something like, enter image description here

Ice76
  • 1,143
  • 8
  • 16
  • 1
    The best solution is to not use comma-separated values in the first place. Create another table that lists the versions in separate rows. – Barmar Sep 21 '18 at 23:38
  • @Barmar I have a table that lists versions per product. But wont this lead to redundant data if the same word is in the new version, or is that just not avoidable? – Ice76 Sep 21 '18 at 23:44
  • The other table should just have two columns, a foreign key that points to this table's ID, and the version number. No redundant data. – Barmar Sep 21 '18 at 23:48
  • @Barmar Thanks for reminding me.. should of goes the next step in normalization! – Ice76 Sep 22 '18 at 00:02

1 Answers1

1

It seems like you might have some luck with FIND_IN_SET() Probably, something like FIND_IN_SET(fdd.version_id, fdl.version_id) > 0

Uueerdo
  • 15,723
  • 1
  • 16
  • 21