vendredi 10 juin 2016

Join on matching records and create combinations when no match


I am trying to create a new table using two others. I want to create a record all combinations of records while merging a 'value' column. I want another column that tells me where the value is from Something like this.:

A:

Day    Month   Random1    Random2    value1 
01     Jan     AA         XX         12     
24     Mar     BB         YY         34     
13     Feb     CC         ZZ         7

B:

Day    Month   value2
03     Jan     16
24     Mar     2

I am trying to join them on Day and Month, however I want it to create a row with all combinations if there is no row match.

I want a table as follows:

C:

Day    Month   Random1    Random2    value    Type
01     Jan     AA         XX         12       value1
24     Mar     BB         YY         34       value1
13     Feb     CC         ZZ         7        value1
03     Jan     AA         XX         16       value2
03     Jan     AA         YY         16       value2
03     Jan     AA         ZZ         16       value2
03     Jan     BB         XX         16       value2
03     Jan     BB         YY         16       value2
03     Jan     BB         ZZ         16       value2
03     Jan     CC         XX         16       value2
03     Jan     CC         YY         16       value2
03     Jan     CC         ZZ         16       value2
24     Mar     AA         XX         2        value2
24     Mar     AA         YY         2        value2
24     Mar     AA         ZZ         2        value2
24     Mar     BB         XX         2        value2
24     Mar     BB         YY         2        value2
24     Mar     BB         ZZ         2        value2
24     Mar     CC         XX         2        value2
24     Mar     CC         YY         2        value2
24     Mar     CC         ZZ         2        value2

My sql query is unusually complicated and am sure there is a simple way of doing this. (I am actually writing this query in R using sqldf(), I looking for an sql suggestion but am open to alternatives.)


Aucun commentaire:

Enregistrer un commentaire