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