Create a column from query data

Question

Test data:

create temp table l (id integer,name text); create temp table t (id integer); create temp table t_i18n(id integer,l_id integer,t_id integer,name text); insert into l(id, name) values (1, 'lang_1'), (2, 'lang_2'); insert into t(id) values(1); insert into t_i18n(id, l_id, t_id, name) values (1, 1, 1, 'Text in the language one'), (2, 2, 1, 'Text in the language two'); 

After executing this request:

 select * from t inner join t_i18n i18n on i18n.t_id = t.id; 

I have this result:

  id | id | l_id | t_id | name ----+----+------+------+-------------------------- 1 | 1 | 1 | 1 | Text in the language one 1 | 2 | 2 | 1 | Text in the language two 

Is it possible to modify the query above to get the result below?

 /*Expected result*/ id | name_lang_1 | name_lang_2 ----+--------------------------+-------------------------- 1 | Text in the language one | Text in the language two 

Answer 1

Usually crosstab() will be faster. You need the optional tablefunc module installed in your database.

 SELECT * FROM crosstab( 'SELECT t.id, l.name AS lang_name, i.name AS lang FROM t JOIN t_i18n i ON i.t_id = t.id JOIN l ON l.id = i.l_id' -- could also just be "ORDER BY 1" here ,$$VALUES ('lang_1'::text), ('lang_2')$$) AS l (id text, lang_1 text, lang_2 text); 

If your case is actually as simple (hardly ever), a query with CASE statements will do:

 SELECT t.id , min(CASE WHEN i.l_id = 1 THEN i.name ELSE NULL END) AS lang_1 , min(CASE WHEN i.l_id = 2 THEN i.name ELSE NULL END) AS lang_2 FROM t LEFT JOIN t_i18n i ON i.t_id = t.id LEFT JOIN l ON l.id = i.l_id GROUP BY 1 ORDER BY 1; 

Details for both decisions on this related issue:
PostgreSQL Cross Forward Request

Since each SQL query and each function must have a clearly defined return type, this cannot be done for the dynamic number of language names in one query. You can write a function to dynamically create a statement and execute the statement in the second call.

There are also advanced methods with polymorphic types, I wrote an exhaustive answer here:
A dynamic alternative for turning with CASE and GROUP BY

However, the simple crosstab() query above works well with a superset of language names. Fields for non-existent languages ​​return NULL . Check out the link provided .

Answer 2

Use self-join

 select t1.id, i18n1.l_id l_id1, i18n1.t_id t_id1, i18n1.name name1, i18n2.l_id l_id2, i18n2.t_id t_id2, i18n2.name name2 from t t1 inner join t_i18n i18n1 on i18n1.t_id = t1.id and i18n1.l_id = 1 inner join t t2 on t1.id = t2.id inner join t_i18n i18n2 on i18n2.t_id = t2.id and i18n2.l_id = 2 ; 

However, it only works if each entry in table t always has exactly two corresponding entries in table t_i18n (one entry for each language).
If in some entries there can be only one language (lang-1 or lang -2), then you should use external connections, as in this query:

 select t1.id, i18n1.l_id l_id1, i18n1.t_id t_id1, i18n1.name name1, i18n2.l_id l_id2, i18n2.t_id t_id2, i18n2.name name2 from t t1 left join t_i18n i18n1 on i18n1.t_id = t1.id and i18n1.l_id = 1 full outer join t t2 on t1.id = t2.id left join t_i18n i18n2 on i18n2.t_id = t2.id and i18n2.l_id = 2 

Here is a SQLFiddle demo with sample data for the second case and both queries, see how they work with this data.