Is possible to reuse subqueries?

Question

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 

Answer 1

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 

Answer 2

Some of later database products (Oracle, SQL Server 2005, SQL Server 2008 and so on) provide the ability to create common table expressions (CTE for short). With that you could reuse a subquery like so:

With Subquery As     (     Select Id         , Min( Case When T.TypeId = 1 Then Value End ) As MinType1         , Min( Case When T.TypeId = 2 Then Value End ) As MinType2         , Min( Case When T.TypeId = 3 Then Value End ) As MinType3         , Min( Case When T.TypeId = 4 Then Value End ) As MinType4     From Table2 As T     Where T.Type In(1,2,3,4)         And T.Value Between '2010-01-01' And '2010-01-07'     Group By Id     ) Select A.Id, A.Name, S.MinType1, S.MinType2, S.MinType3, S.MinType4 From Subquery As S     Left Join Table As A         On A.Id = S.Id 

Granted this wouldn't be much different than:

Select  A.Id, A.Name, S.MinType1, S.MinType2, S.MinType3, S.MinType4 From    (         Select Id             , Min( Case When T.TypeId = 1 Then Value End ) As MinType1             , Min( Case When T.TypeId = 2 Then Value End ) As MinType2             , Min( Case When T.TypeId = 3 Then Value End ) As MinType3             , Min( Case When T.TypeId = 4 Then Value End ) As MinType4         From Table2 As T         Where T.Type In(1,2,3,4)             And T.Value Between '2010-01-01' And '2010-01-07'         Group By Id         ) As S     Left Join Table As A         On A.Id = S.Id