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  |

I believe this will help. http://stackoverflow.com/questions/8283541/merging-multiple-rows-into-one-row-and-multiple-columns-on-mysql — colonelclick, Jun 08 '12 at 17:49

score 1 · Answer 1 · answered Jun 08 '12 at 18:26

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.

answered Jun 08 '12 at 18:26

shreyas

178
1
9

1

Thank you , however I am not trying to concatenate values into a string, but to reassemble values from different rows (which have in common only a reference_id) into a new database row such as | ID | Title | Fulltext | Metadesc | – nobody Jun 08 '12 at 21:58
1

Can you post your table structure of how the values are currently and how you would like it to be. this will make it more clear – shreyas Jun 08 '12 at 22:21

score 0 · Answer 2 · answered Jun 09 '12 at 17:30

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

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

2 Answers2