elmama
​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


Outer joining a table to itself 


Let’s say you have the following table and contents


Year        Period        ID    Value

-------------------------------------------------------
2012        1              1    12
2012        1              2    10
2012        1              3    9
2012        2              1    15
2012        2              2    11
2012        2              4    13


And you want to see the following resultset from it.


Year        ID1    ID2    Value Period 1 ID  Value Period 2 ID
--------------------------------------------------------------------------
2012        1        1                12                    15
2012        2        2                10                    11
2012        3        null              9                    null             
2012        null     4               null                   13


Now, most of you will have guessed that an outer join of some sort is required but it’s not immediately obvious - at least to me - what exactly is required.


Anyway here is one solution.


select a.year,a.ID id1, b.id id2 a.value value1,b.value value2   from

(select year,id,value from t1 where period = 1) a

full join

(select year,id,value from t2 where period = 2) b

on a.year = b.year and b.id = a.id