CODEWITHSUNDEEP
mysqlsql
JNewbie
JNewbie

Reputation: 25

mySQL query for a table with multiple keys

I have a table that has different rows, each row has a different key for the same items. I would like to create a mySQL query that puts the different keys as columns in a new table. Original table:

id  locale  key         value
1   en  description Great trip around cordoba's jklfjklfsjfdsk sdjk ds...
1   en  highlights_1    horse back riding
1   en  highlights_2    Asado
1   en  highlights_3    Free drinks

Requested result:

id  description        highlights_1        highlights_2       highlights_3    ...
1   Great trip ar...   horse back riding   Asado              Free drinks

The query I made (and gets me only one key/column in the new table is...)

SELECT activity.id,
  activity_multilingual_fields_text.value AS activitydescription
  FROM activity
  LEFT JOIN activity_multilingual_fields_text ON activity_multilingual_fields_text.id = activity.id  AND activity_multilingual_fields_text.locale = "en" 
GROUP BY activity.id

I would appreciate your help!

Upvotes: 1

Views: 1010

Answers (2)

Michael Berkowski
Michael Berkowski

Reputation: 270607

This is ultimately not difficult since there is a fixed and finite number of possible values for key. You do not need to dynamically construct the SQL, it can be hard-coded.

The general pattern is to enclose a CASE statement which returns the value column if the value of key matches a predefined string. This needs to be done for each possible key.

That results in 5 separate rows, mostly NULL though. So then you wrap each CASE inside a MAX() aggregate which discards the NULLs and collapses each into a single row. A GROUP BY must be applied over the other columns selected.

SELECT
  activity.id,
  MAX(CASE WHEN lf.`key` = 'description' THEN lf.value ELSE NULL END) AS `description`,
  MAX(CASE WHEN lf.`key` = 'highlights_1' THEN lf.value ELSE NULL END) AS `highlights_1`,
  MAX(CASE WHEN lf.`key` = 'highlights_2' THEN lf.value ELSE NULL END) AS `highlights_2`,
  MAX(CASE WHEN lf.`key` = 'highlights_3' THEN lf.value ELSE NULL END) AS `highlights_3`,
  MAX(CASE WHEN lf.`key` = 'highlights_4' THEN lf.value ELSE NULL END) AS `highlights_4`
FROM 
  activity
  LEFT JOIN activity_multilingual_fields_text lf
    ON lf.id = activity.id
       AND lf.locale = "en" 
GROUP BY activity.id

Here's a demonstration: http://sqlfiddle.com/#!2/bc028/3

Upvotes: 1

dcarrith
dcarrith

Reputation: 7920

The GROUP BY activity.id is going to aggregate the results into 1 result record per unique value in the id column. That's probably why the code you have so far gets you only one key/column in the new table. Beyond that, the example doesn't seem to be complete. Where are you creating the table? Is that something you're doing with whatever server-side language you're using to execute the query?

Upvotes: 0

Related Questions