Joins - Excel Vlookups - Stack Exchange Data Explorer

Joins - Excel Vlookups

Please login or register to vote for this query.

(click on this box to dismiss)
Joins - Excel Vlookups
Q&A for anime and manga fans
create table #UserActivity
(
  ID int identity,
  usernumber int,
  productcode int

)

create table #SpentonAds
(
  ID int identity,
  adnumber int,
  spent decimal
)

create table #LinkProductAd
(
  ID int identity,
  productcode int,
  adnumber int 
)

insert into #UserActivity values (1,58416);
insert into #UserActivity values (2,1247313);
insert into #UserActivity values (3,521113);
insert into #UserActivity values (4,199035);
insert into #UserActivity values (5,650390);
insert into #UserActivity values (6,418090);
insert into #UserActivity values (6,495237);
insert into #UserActivity values (6,495237);
insert into #UserActivity values (7,167866);
insert into #UserActivity values (8,421747);
insert into #UserActivity values (8,592069);
insert into #UserActivity values (9,36922);
insert into #UserActivity values (10,928502);
insert into #UserActivity values (11,1212942);
insert into #UserActivity values (12,1064578);
insert into #UserActivity values (13,440737);
insert into #UserActivity values (14,427855);
insert into #UserActivity values (14,1149859);
insert into #UserActivity values (15,270776);
insert into #UserActivity values (15,324693);
insert into #UserActivity values (15,153180);
insert into #UserActivity values (15,139982);
insert into #UserActivity values (15,1240816);
insert into #UserActivity values (15,1241294);
insert into #UserActivity values (16,958359);
insert into #UserActivity values (16,1076278);
insert into #UserActivity values (16,1190037);
insert into #UserActivity values (16,1197100);
insert into #UserActivity values (16,1236594);
insert into #UserActivity values (16,1261709);
insert into #UserActivity values (17,855443);
insert into #UserActivity values (18,166224);
insert into #UserActivity values (19,408328);
insert into #UserActivity values (19,1037073);
insert into #UserActivity values (20,1131693);
insert into #UserActivity values (20,1149136);
insert into #UserActivity values (21,650377);
insert into #UserActivity values (22,1066136);
insert into #UserActivity values (23,670001);
insert into #UserActivity values (24,896115);
insert into #UserActivity values (24,928489);
insert into #UserActivity values (25,196357);
insert into #UserActivity values (26,133525);
insert into #UserActivity values (26,902411);
insert into #UserActivity values (26,54967);
insert into #UserActivity values (26,103284);
insert into #UserActivity values (27,170336);
insert into #UserActivity values (28,592762);
insert into #UserActivity values (29,658290);
insert into #UserActivity values (29,737509);
insert into #UserActivity values (29,1035553);
insert into #UserActivity values (29,1159127);
insert into #UserActivity values (29,1159127);
insert into #UserActivity values (30,716545);
insert into #UserActivity values (31,458317);
insert into #UserActivity values (32,272434);
insert into #UserActivity values (33,912329);
insert into #UserActivity values (34,1257791);
insert into #UserActivity values (35,908662);
insert into #UserActivity values (35,946533);
insert into #UserActivity values (36,204289);
insert into #UserActivity values (37,877552);
insert into #UserActivity values (38,912145);
insert into #UserActivity values (39,108183);
insert into #UserActivity values (40,877930);
insert into #UserActivity values (40,878089);
insert into #UserActivity values (40,884196);
insert into #UserActivity values (41,778823);
insert into #UserActivity values (42,1188879);
insert into #UserActivity values (43,841714);
insert into #UserActivity values (44,204704);
insert into #UserActivity values (45,705070);
insert into #UserActivity values (46,535563);
insert into #UserActivity values (47,1198255);
insert into #UserActivity values (48,1231082);
insert into #UserActivity values (48,1211007);
insert into #UserActivity values (49,1162113);
insert into #UserActivity values (49,1162113);
insert into #UserActivity values (49,1197172);
insert into #UserActivity values (49,1197172);
insert into #UserActivity values (49,1225455);
insert into #UserActivity values (50,850606);
insert into #UserActivity values (51,250319);
insert into #UserActivity values (52,863862);
insert into #UserActivity values (53,700999);
insert into #UserActivity values (54,590822);
insert into #UserActivity values (55,227291);
insert into #UserActivity values (56,1011712);
insert into #UserActivity values (57,406859);
insert into #UserActivity values (58,187929);
insert into #UserActivity values (59,790499);
insert into #UserActivity values (60,1027196);
insert into #UserActivity values (61,447951);
insert into #UserActivity values (62,681294);
insert into #UserActivity values (63,461676);
insert into #UserActivity values (63,227609);
insert into #UserActivity values (63,638866);
insert into #UserActivity values (63,152073);
insert into #UserActivity values (63,687002);
insert into #UserActivity values (63,34136);

insert into #SpentonAds values (  10592  ,  4696.92  )  ;
insert into #SpentonAds values (  10860  ,  18787.68  )  ;
insert into #SpentonAds values (  10929  ,  9393.37  )  ;
insert into #SpentonAds values (  11025  ,  6629.39  )  ;
insert into #SpentonAds values (  11149  ,  2349.56  )  ;
insert into #SpentonAds values (  11152  ,  6629.18  )  ;
insert into #SpentonAds values (  11178  ,  6265.56  )  ;
insert into #SpentonAds values (  11503  ,  9398.24  )  ;
insert into #SpentonAds values (  12106  ,  3757.36  )  ;
insert into #SpentonAds values (  12123  ,  3757.76  )  ;
insert into #SpentonAds values (  12135  ,  6629.39  )  ;
insert into #SpentonAds values (  12142  ,  6629.39  )  ;
insert into #SpentonAds values (  12183  ,  6629.09  )  ;
insert into #SpentonAds values (  12202  ,  3757.46  )  ;
insert into #SpentonAds values (  12834  ,  6629.10  )  ;
insert into #SpentonAds values (  13032  ,  2348.46  )  ;
insert into #SpentonAds values (  13255  ,  6262.56  )  ;
insert into #SpentonAds values (  13276  ,  3757.54  )  ;
insert into #SpentonAds values (  13369  ,  2087.45  )  ;
insert into #SpentonAds values (  13853  ,  3132.78  )  ;
insert into #SpentonAds values (  13949  ,  9395.43  )  ;
insert into #SpentonAds values (  14035  ,  9393.67  )  ;
insert into #SpentonAds values (  14163  ,  3133.61  )  ;
insert into #SpentonAds values (  14653  ,  2348.55  )  ;
insert into #SpentonAds values (  14721  ,  3131.45  )  ;
insert into #SpentonAds values (  14763  ,  3131.27  )  ;
insert into #SpentonAds values (  14766  ,  3132.76  )  ;
insert into #SpentonAds values (  14768  ,  6262.56  )  ;
insert into #SpentonAds values (  14771  ,  6262.26  )  ;
insert into #SpentonAds values (  14814  ,  6632.47  )  ;
insert into #SpentonAds values (  14822  ,  2088.52  )  ;
insert into #SpentonAds values (  14823  ,  3131.28  )  ;
insert into #SpentonAds values (  14829  ,  9395.84  )  ;
insert into #SpentonAds values (  14898  ,  6263.02  )  ;
insert into #SpentonAds values (  15737  ,  2683.95  )  ;
insert into #SpentonAds values (  16226  ,  3131.28  )  ;
insert into #SpentonAds values (  16471  ,  3131.14  )  ;
insert into #SpentonAds values (  16792  ,  3131.28  )  ;
insert into #SpentonAds values (  17216  ,  2683.95  )  ;
insert into #SpentonAds values (  17809  ,  18787.68  )  ;
insert into #SpentonAds values (  17936  ,  3757.48  )  ;
insert into #SpentonAds values (  18866  ,  3131.12  )  ;
insert into #SpentonAds values (  18943  ,  6629.36  )  ;
insert into #SpentonAds values (  19040  ,  4696.92  )  ;
insert into #SpentonAds values (  19674  ,  2683.83  )  ;
insert into #SpentonAds values (  20052  ,  2349.59  )  ;
insert into #SpentonAds values (  20257  ,  6632.56  )  ;
insert into #SpentonAds values (  20881  ,  6263.46  )  ;
insert into #SpentonAds values (  21414  ,  18787.65  )  ;
insert into #SpentonAds values (  21574  ,  2087.71  )  ;
insert into #SpentonAds values (  21604  ,  6262.75  )  ;
insert into #SpentonAds values (  21632  ,  9393.35  )  ;
insert into #SpentonAds values (  21675  ,  2348.36  )  ;
insert into #SpentonAds values (  21830  ,  2087.42  )  ;
insert into #SpentonAds values (  22641  ,  2683.85  )  ;
insert into #SpentonAds values (  22661  ,  9393.84  )  ;
insert into #SpentonAds values (  22732  ,  18786.93  )  ;
insert into #SpentonAds values (  22937  ,  2348.59  )  ;
insert into #SpentonAds values (  22939  ,  3131.45  )  ;
insert into #SpentonAds values (  23230  ,  2348.35  )  ;
insert into #SpentonAds values (  23409  ,  2683.83  )  ;
insert into #SpentonAds values (  23578  ,  6262.49  )  ;
insert into #SpentonAds values (  23579  ,  4697.42  )  ;
insert into #SpentonAds values (  23862  ,  6262.31  )  ;
insert into #SpentonAds values (  23864  ,  6262.32  )  ;
insert into #SpentonAds values (  24318  ,  18796.58  )  ;
insert into #SpentonAds values (  25358  ,  18786.97  )  ;
insert into #SpentonAds values (  28821  ,  2087.49  )  ;
insert into #SpentonAds values (  30413  ,  18787.68  )  ;
insert into #SpentonAds values (  30942  ,  2348.39  )  ;
insert into #SpentonAds values (  31205  ,  4698.24  )  ;
insert into #SpentonAds values (  32526  ,  4696.92  )  ;
insert into #SpentonAds values (  32729  ,  4696.92  )  ;
insert into #SpentonAds values (  33804  ,  3131.45  )  ;
insert into #SpentonAds values (  37952  ,  3757.74  )  ;
insert into #SpentonAds values (  39446  ,  18788.68  )  ;
insert into #SpentonAds values (  39770  ,  4696.78  )  ;
insert into #SpentonAds values (  40187  ,  2087.74  )  ;
insert into #SpentonAds values (  40188  ,  2087.41  )  ;
insert into #SpentonAds values (  40622  ,  3757.54  )  ;
insert into #SpentonAds values (  41101  ,  2685.24  )  ;
insert into #SpentonAds values (  42637  ,  4696.76  )  ;
insert into #SpentonAds values (  43171  ,  2087.46  )  ;
insert into #SpentonAds values (  43301  ,  3757.51  )  ;
insert into #SpentonAds values (  46351  ,  3131.13  )  ;
insert into #SpentonAds values (  46421  ,  3757.34  )  ;
insert into #SpentonAds values (  47310  ,  4696.83  )  ;
insert into #SpentonAds values (  49797  ,  2683.95  )  ;
insert into #SpentonAds values (  50447  ,  2087.51  )  ;
insert into #SpentonAds values (  50485  ,  2683.81  )  ;
insert into #SpentonAds values (  51220  ,  2683.95  )  ;
insert into #SpentonAds values (  51230  ,  3757.37  )  ;
insert into #SpentonAds values (  51980  ,  3131.22  )  ;
insert into #SpentonAds values (  52381  ,  2685.24  )  ;
insert into #SpentonAds values (  52938  ,  6632.56  )  ;
insert into #SpentonAds values (  52995  ,  9398.32  )  ;
insert into #SpentonAds values (  53837  ,  3759.34  )  ;
insert into #SpentonAds values (  54698  ,  3757.92  )  ;
insert into #SpentonAds values (  56195  ,  9398.34  )  ;
insert into #SpentonAds values (  56206  ,  3759.34  )  ;

insert into #LinkProductAd values (  34136  ,  16471.00  )  ;
insert into #LinkProductAd values (  36922  ,  21414.00  )  ;
insert into #LinkProductAd values (  54967  ,  23862.00  )  ;
insert into #LinkProductAd values (  58416  ,  10592.00  )  ;
insert into #LinkProductAd values (  103284  ,  23864.00  )  ;
insert into #LinkProductAd values (  108183  ,  18943.00  )  ;
insert into #LinkProductAd values (  133525  ,  11178.00  )  ;
insert into #LinkProductAd values (  139982  ,  17936.00  )  ;
insert into #LinkProductAd values (  152073  ,  14771.00  )  ;
insert into #LinkProductAd values (  153180  ,  14823.00  )  ;
insert into #LinkProductAd values (  166224  ,  12183.00  )  ;
insert into #LinkProductAd values (  167866  ,  11149.00  )  ;
insert into #LinkProductAd values (  170336  ,  10929.00  )  ;
insert into #LinkProductAd values (  187929  ,  10860.00  )  ;
insert into #LinkProductAd values (  196357  ,  11152.00  )  ;
insert into #LinkProductAd values (  199035  ,  51980.00  )  ;
insert into #LinkProductAd values (  204289  ,  11025.00  )  ;
insert into #LinkProductAd values (  204704  ,  12135.00  )  ;
insert into #LinkProductAd values (  227291  ,  20052.00  )  ;
insert into #LinkProductAd values (  227609  ,  14766.00  )  ;
insert into #LinkProductAd values (  250319  ,  41101.00  )  ;
insert into #LinkProductAd values (  270776  ,  14814.00  )  ;
insert into #LinkProductAd values (  272434  ,  11503.00  )  ;
insert into #LinkProductAd values (  324693  ,  14822.00  )  ;
insert into #LinkProductAd values (  406859  ,  14035.00  )  ;
insert into #LinkProductAd values (  408328  ,  13032.00  )  ;
insert into #LinkProductAd values (  418090  ,  12106.00  )  ;
insert into #LinkProductAd values (  421747  ,  12202.00  )  ;
insert into #LinkProductAd values (  427855  ,  13276.00  )  ;
insert into #LinkProductAd values (  440737  ,  12142.00  )  ;
insert into #LinkProductAd values (  447951  ,  12123.00  )  ;
insert into #LinkProductAd values (  458317  ,  19674.00  )  ;
insert into #LinkProductAd values (  461676  ,  14763.00  )  ;
insert into #LinkProductAd values (  495237  ,  13369.00  )  ;
insert into #LinkProductAd values (  495237  ,  14721.00  )  ;
insert into #LinkProductAd values (  521113  ,  14829.00  )  ;
insert into #LinkProductAd values (  535563  ,  12834.00  )  ;
insert into #LinkProductAd values (  590822  ,  13255.00  )  ;
insert into #LinkProductAd values (  592069  ,  17216.00  )  ;
insert into #LinkProductAd values (  592762  ,  39770.00  )  ;
insert into #LinkProductAd values (  638866  ,  14768.00  )  ;
insert into #LinkProductAd values (  650377  ,  18866.00  )  ;
insert into #LinkProductAd values (  650390  ,  13949.00  )  ;
insert into #LinkProductAd values (  658290  ,  13853.00  )  ;
insert into #LinkProductAd values (  670001  ,  14163.00  )  ;
insert into #LinkProductAd values (  681294  ,  14653.00  )  ;
insert into #LinkProductAd values (  687002  ,  14898.00  )  ;
insert into #LinkProductAd values (  700999  ,  19040.00  )  ;
insert into #LinkProductAd values (  705070  ,  15737.00  )  ;
insert into #LinkProductAd values (  716545  ,  16226.00  )  ;
insert into #LinkProductAd values (  737509  ,  16792.00  )  ;
insert into #LinkProductAd values (  778823  ,  17809.00  )  ;
insert into #LinkProductAd values (  790499  ,  22661.00  )  ;
insert into #LinkProductAd values (  841714  ,  20257.00  )  ;
insert into #LinkProductAd values (  850606  ,  21675.00  )  ;
insert into #LinkProductAd values (  855443  ,  22641.00  )  ;
insert into #LinkProductAd values (  863862  ,  20881.00  )  ;
insert into #LinkProductAd values (  877552  ,  21574.00  )  ;
insert into #LinkProductAd values (  877930  ,  21604.00  )  ;
insert into #LinkProductAd values (  878089  ,  21632.00  )  ;
insert into #LinkProductAd values (  884196  ,  21830.00  )  ;
insert into #LinkProductAd values (  896115  ,  23230.00  )  ;
insert into #LinkProductAd values (  902411  ,  22732.00  )  ;
insert into #LinkProductAd values (  908662  ,  23409.00  )  ;
insert into #LinkProductAd values (  912145  ,  22937.00  )  ;
insert into #LinkProductAd values (  912329  ,  22939.00  )  ;
insert into #LinkProductAd values (  928489  ,  23579.00  )  ;
insert into #LinkProductAd values (  928502  ,  23578.00  )  ;
insert into #LinkProductAd values (  946533  ,  24318.00  )  ;
insert into #LinkProductAd values (  958359  ,  25358.00  )  ;
insert into #LinkProductAd values (  1011712  ,  28821.00  )  ;
insert into #LinkProductAd values (  1027196  ,  30413.00  )  ;
insert into #LinkProductAd values (  1035553  ,  30942.00  )  ;
insert into #LinkProductAd values (  1037073  ,  31205.00  )  ;
insert into #LinkProductAd values (  1064578  ,  32526.00  )  ;
insert into #LinkProductAd values (  1066136  ,  32729.00  )  ;
insert into #LinkProductAd values (  1076278  ,  33804.00  )  ;
insert into #LinkProductAd values (  1131693  ,  37952.00  )  ;
insert into #LinkProductAd values (  1149136  ,  54698.00  )  ;
insert into #LinkProductAd values (  1149859  ,  39446.00  )  ;
insert into #LinkProductAd values (  1159127  ,  40187.00  )  ;
insert into #LinkProductAd values (  1159127  ,  40188.00  )  ;
insert into #LinkProductAd values (  1162113  ,  40622.00  )  ;
insert into #LinkProductAd values (  1162113  ,  43301.00  )  ;
insert into #LinkProductAd values (  1188879  ,  42637.00  )  ;
insert into #LinkProductAd values (  1190037  ,  43171.00  )  ;
insert into #LinkProductAd values (  1197100  ,  46351.00  )  ;
insert into #LinkProductAd values (  1197172  ,  46421.00  )  ;
insert into #LinkProductAd values (  1197172  ,  50447.00  )  ;
insert into #LinkProductAd values (  1198255  ,  47310.00  )  ;
insert into #LinkProductAd values (  1211007  ,  51230.00  )  ;
insert into #LinkProductAd values (  1212942  ,  49797.00  )  ;
insert into #LinkProductAd values (  1225455  ,  50485.00  )  ;
insert into #LinkProductAd values (  1231082  ,  51220.00  )  ;
insert into #LinkProductAd values (  1236594  ,  52381.00  )  ;
insert into #LinkProductAd values (  1240816  ,  52938.00  )  ;
insert into #LinkProductAd values (  1241294  ,  52995.00  )  ;
insert into #LinkProductAd values (  1247313  ,  53837.00  )  ;
insert into #LinkProductAd values (  1257791  ,  56195.00  )  ;
insert into #LinkProductAd values (  1261709  ,  56206.00  )  ;
created nov 4 11
aleksandr.gordon
Enter Parameters

Options: Text-only results Include execution plan
Switch to meta site
Hold tight while we fetch your results
Results[:current/:total] Messages Graph Execution Plan
Download CSV Download XML
:records returned in :time ms:cached