Two rows to one row in mysql

Question

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! ...

Answer 1

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