[SOLVED] Create a new column based on values in the same table

Create a new column based on values in the same table

This is an idea of how my table looks -

no	country	language	translatedName
1	c1	en	test1EN1
1	c1	l1	testL1
2	c2	en	test1EN2
2	c2	l2	testL2

This is how the data might look like where a country can have multiple rows with the same no (not a primary key) and value in the country column. There will be multiple rows with multiple languages and their respective translated names.

I have to get an output where I can output all of the information above and additionally a new column called for e.g name which will always be the value in the translatedName for the language en. Something like this -

no	country	language	translatedName	name
1	c1	en	test1EN1	test1EN1
1	c1	l1	testL1	test1EN1
2	c2	en	test1EN2	test1EN2
2	c2	l2	testL2	test1EN2

Any ideas on how I can achieve this - maybe using conditions (CASE) or self-join? Any help is appreciated!

Solution

SELECT t1.*, t2.translatedName AS name
FROM table t1
JOIN table t2 USING (no, country)
WHERE t2.language = 'en'

The query assumes that the row with language = 'en' exists for each (no, country) values pair.