I'm having some problems trying to perform a query. I have two tables, one with elements information, and another one with records related with the elements of the first table. The idea is to get in the same row the element information plus several records information.
Structure could be explain like this:
table [ id, name ]
[1, '1'], [2, '2']
table2 [ id, type, value ]
[1, 1, '2009-12-02']
[1, 2, '2010-01-03']
[1, 4, '2010-01-03']
[2, 1, '2010-01-02']
[2, 2, '2010-01-02']
[2, 2, '2010-01-03']
[2, 3, '2010-01-07']
[2, 4, '2010-01-07']
And this is want I would like to achieve:
result [id, name, Column1, Column2, Column3, Column4]
[1, '1', '2009-12-02', '2010-01-03', , '2010-01-03']
[2, '2', '2010-01-02', '2010-01-02', '2010-01-07', '2010-01-07']
The following query gets the proper result, but it seems to me extremely inefficient, having to iterate table2 for each column. Would be possible in anyway to do a subquery and reuse it?
SELECT
a.id,
a.name,
(select min(value) from table2 t where t.id = subquery.id and t.type = 1 group by t.type) as Column1,
(select min(value) from table2 t where t.id = subquery.id and t.type = 2 group by t.type) as Column2,
(select min(value) from table2 t where t.id = subquery.id and t.type = 3 group by t.type) as Column3,
(select min(value) from table2 t where t.id = subquery.id and t.type = 4 group by t.type) as Column4
FROM
(SELECT distinct id
FROM table2 t
WHERE (t.type in (1, 2, 3, 4))
AND t.value between '2010-01-01' and '2010-01-07') as subquery
LEFT JOIN table a ON a.id = subquery.id
You can take the aggregations out into a CTE (common table expression):
with minima as (select t.id, t.type, min(value) min_value
from table2 t
where t.type in (1,2,3,4)
group by t.id, t.type)
select a.id, a.name,
(select min_value from minima where minima.id = subquery.id and minima.type = 1) as column1,
(select min_value from minima where minima.id = subquery.id and minima.type = 2) as column2,
(select min_value from minima where minima.id = subquery.id and minima.type = 3) as column3,
(select min_value from minima where minima.id = subquery.id and minima.type = 4) as column4
from (select distinct id from table2 t where t.type in (1,2,3,4) and t.value between '2010-01-01' and '2010-01-07') as subquery
left join a on a.id = subquery.id
Whether this is actually any benefit (or even supported) or not depends on your environment and dataset, of course.
Another approach:
select xx.id, a.name, xx.column1, xx.column2, xx.column3, xx.column4
from (
select id,
max(case type when 1 then min_value end) as column1,
max(case type when 2 then min_value end) as column2,
max(case type when 3 then min_value end) as column3,
max(case type when 4 then min_value end) as column4
from (select t.id, t.type, min(value) min_value
from table2 t
where t.type in (1,2,3,4)
group by t.id, t.type) minima
group by id
) xx left join a on a.id = xx.id
order by 1