Hello, 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
