0

Lets say i have this table

name lang message
welcome en Welcome, {user}!...
welcome ko KoreanMessage1
error en Error occurred!....
error ko KoreanMessage2

so i want to select this table like this:

name ko en
welcome KoreanMessage1 Welcome, {user}!...
error KoreanMessage2 Error occurred!...

so how can i do this only with sql? (i'm using mariadb)

i tried this

(SELECT
a.name, a.value AS ko, b.value AS en
FROM messages AS a
LEFT JOIN messages AS b
ON a.name = b.name AND a.lang = 'ko' AND b.lang = 'en')
UNION
(SELECT
a.name, a.value AS ko, b.value AS en
FROM messages AS a
RIGHT JOIN messages AS b
ON a.name = b.name AND a.lang = 'ko' AND b.lang = 'en')
ORDER BY name ASC

(the table is "message")

and it didnt work. there was same columns twice.

oh and there might be some data that only in one language but i want to select that too with null on other language. for example like

name ko en
welcome NULL Welcome! ...
LittleGiqnt
  • 3
  • 3
  • Does this answer your question? [MySQL - Rows to Columns](https://stackoverflow.com/questions/1241178/mysql-rows-to-columns) – Jorge Campos Feb 02 '22 at 02:14

2 Answers2

1

Kindly try this

SELECT m.name,
       msg1.message en,
       msg2.message ko 
FROM messages m 
LEFT JOIN messages msg1 
    ON msg1.name = m.name 
    AND msg1.lang = 'en' 
LEFT JOIN messages msg2 
    ON msg2.name = m.name 
    AND msg2.lang = 'ko' 
GROUP BY m.name
ruleboy21
  • 5,510
  • 4
  • 17
  • 34
0

I think you can use join and group by to get what you want.

SELECT m.name, m_en.message en, m_ko.message ko
FROM messages m
INNER JOIN messages m_en ON m_en.name = m.name
INNER JOIN messages m_ko ON m_ko.name = m.name
WHERE m_en.lang = 'en'
AND m_ko.lang = 'ko'
GROUP BY m.name, en, ko
Ady
  • 190
  • 1
  • 6
  • well yeah that kind of works better than mine but as i said: there might be some data that only in one language but i want to select that too with null on other language. – LittleGiqnt Feb 02 '22 at 02:05
  • It works too. If the default value is NULL you'll get NULL. http://sqlfiddle.com/#!9/bdb749/1/0 – Ady Feb 02 '22 at 02:17
  • oh i meant there might be no row with other language http://sqlfiddle.com/#!9/03deec/1 – LittleGiqnt Feb 02 '22 at 02:29
  • Ok... A bit more complicated! I don't know if it's possible, but I think the best practice is to insert both in all cases, with default value if the message does not exist. – Ady Feb 02 '22 at 02:51
  • yeah i was thinking i should change my table design too. well thanks for trying to help anyway! – LittleGiqnt Feb 02 '22 at 02:56