SQL query to extract field values from rows sharing same value in specific column, and combine those value into a single row

Question

I am trying to export translated content from Joomla's Joomfish MySQL database table.

The way Joomfish works is by translating fields separately and storing them in a jos_jf_content table with a reference id to the original source content from jos_content table. For example, one row may hold the translated title, another row the translated categories, and another the fulltext content. These rows are separate and only share the reference_id value.

So one has a jos_jf_content table where the columns of interest are:

reference_id (id of source) | reference_field (title|fulltext|metadesc) | value (translation)

I need to combine all the values sharing the same reference_id into a single row whose columns are Title, Fulltext, Metadesc etc .

Any hint?

UPDATE:

Original table is like:

|reference_id|   reference_field |   value        |

        10          title               A title
        10          fulltext           Body of post

I need to export it (CSV etc) as:

|  ID    |  TITLE    |    FULLTEXT     | 

|  10    |   A Title |  Body of post  |

Answer 1

you can use Mysql GROUP_CONCAT() so your query might look like

SELECT group_concat(column_name) FROM table where reference_id = 'rid'

assuming the column name used to save these information is same. if not please paste your table structure. if they stored in different columns you can use concat too.

Answer 2

I think then the following query fits the objective. Thanks to Bill Purchase for the link to a similar situation. (note I ended up needing the introtext only instead of the fulltext)

    SELECT DISTINCT jf.reference_id
   , t.value AS title
   , it.value AS introtext
   , mk.value AS metakey
   , md.value AS metadesc
FROM jos_jf_content AS jf
LEFT JOIN jos_jf_content AS t ON jf.reference_id = t.reference_id
  AND t.reference_field = 'title' AND t.reference_table='content'
LEFT JOIN jos_jf_content AS it ON jf.reference_id = it.reference_id
  AND it.reference_field = 'introtext' AND it.reference_table='content'
LEFT JOIN jos_jf_content AS mk ON jf.reference_id = mk.reference_id
  AND mk.reference_field = 'metakey' AND mk.reference_table='content'
LEFT JOIN jos_jf_content AS md ON jf.reference_id = md.reference_id
  AND md.reference_field = 'metadesc' AND md.reference_table='content'
WHERE jf.reference_table='content'
ORDER BY jf.reference_id