How do I 'Join' two Structured Datasets?

Question

Consider the following Datasets

dsCompetitions with fields {"id","name","eventTypeId"}

dsEventTypes with fields {"id", "name"}

I want to join the datasets as follows using SQL pseudocode:

SELECT c.id, c.name, e.name
FROM dsCompetions c, dsEventTypes e
WHERE c.eventTypeId = e.id;

Resulting in a

Dataset with fields {"id", "name", "eventtype"}

I read the document about Datasets (JoinAcross) and tried JoinAcross but with no success.

See also: JoinAcross Crashes Kernel in Version 11.0

BEGIN TEST DATA ( USE COPY / PASTE TO NOTEBOOK )

dsEventTypes1 = Dataset[{<|"id" -> 1, "name" -> "Soccer"|>, <|"id" -> 2, "name" -> "Tennis"|>, <|"id" -> 3, "name" -> "Golf"|>, <|"id" -> 4, "name" -> "Cricket"|>, <|"id" -> 5, "name" -> "Rugby Union"|>, <|"id" -> 1477, "name" -> "Rugby League"|>, <|"id" -> 6, "name" -> "Boxing"|>, <|"id" -> 7, "name" -> "Horse Racing"|>, <|"id" -> 8, "name" -> "Motor Sport"|>, <|"id" -> 27454571, "name" -> "E-Sports"|>, <|"id" -> 998918, "name" -> "Bowls"|>, <|"id" -> 10, "name" -> "Special Bets"|>, <|"id" -> 998917, "name" -> "Volleyball"|>, <|"id" -> 11, "name" -> "Cycling"|>, <|"id" -> 136332, "name" -> "Chess"|>, <|"id" -> 315220, "name" -> "Poker"|>, <|"id" -> 3988, "name" -> "Athletics"|>, <|"id" -> 6422, "name" -> "Snooker"|>, <|"id" -> 6231, "name" -> "Financial Bets"|>, <|"id" -> 6423, "name" -> "American Football"|>, <|"id" -> 7511, "name" -> "Baseball"|>, <|"id" -> 27829360, "name" -> "MMA / UFC"|>, <|"id" -> 606611, "name" -> "Netball"|>, <|"id" -> 451485, "name" -> "Winter Sports"|>, <|"id" -> 7522, "name" -> "Basketball"|>, <|"id" -> 7524, "name" -> "Ice Hockey"|>, <|"id" -> 61420, "name" -> "Australian Rules"|>, <|"id" -> 468328, "name" -> "Handball"|>, <|"id" -> 3503, "name" -> "Darts"|>, <|"id" -> 4339, "name" -> "Greyhound Racing"|>, <|"id" -> 2378961, "name" -> "Politics"|>, <|"id" -> 72382, "name" -> "Pool"|>}]

dsCompetitions1 = Dataset[{<|"id" -> 4515398, "name" -> "T&T Pro League", "eventTypeId" -> 1|>, <|"id" -> 7, "name" -> "Austrian Bundesliga", "eventTypeId" -> 1|>, <| "id" -> 9, "name" -> "Erste Liga", "eventTypeId" -> 1|>, <| "id" -> 11, "name" -> "Dutch Jupiler League", "eventTypeId" -> 1|>, <|"id" -> 13, "name" -> "Campeonato", "eventTypeId" -> 1|>, <|"id" -> 15, "name" -> "Bulgarian Premier", "eventTypeId" -> 1|>, <|"id" -> 17, "name" -> "Croatian Division 1", "eventTypeId" -> 1|>, <| "id" -> 4431442, "name" -> "Algerian Ligue 1", "eventTypeId" -> 1|>, <|"id" -> 1587980, "name" -> "Lithuanian 1 Lyga", "eventTypeId" -> 1|>, <| "id" -> 4732508, "name" -> "Uzbekistan Youth League U23", "eventTypeId" -> 1|>, <|"id" -> 21, "name" -> "Synot Liga", "eventTypeId" -> 1|>, <|"id" -> 23, "name" -> "Danish Superliga", "eventTypeId" -> 1|>, <|"id" -> 8580762, "name" -> "Liga Bet - South B", "eventTypeId" -> 1|>, <| "id" -> 25, "name" -> "Danish Division 1", "eventTypeId" -> 1|>, <| "id" -> 821269, "name" -> "Tercera Division", "eventTypeId" -> 1|>, <|"id" -> 9404054, "name" -> "Dutch Eredivisie", "eventTypeId" -> 1|>, <| "id" -> 872215, "name" -> "Ukrainian Persha Liga", "eventTypeId" -> 1|>, <|"id" -> 7511144, "name" -> "Argentinian Primera C Metropolitana", "eventTypeId" -> 1|>, <|"id" -> 7946082, "name" -> "Ukrainian U21 League", "eventTypeId" -> 1|>, <| "id" -> 842258, "name" -> "Qatari Stars League", "eventTypeId" -> 1|>, <|"id" -> 31, "name" -> "English Premier League", "eventTypeId" -> 1|>, <| "id" -> 35, "name" -> "League One", "eventTypeId" -> 1|>, <| "id" -> 6480193, "name" -> "Nicaraguan Premier Division", "eventTypeId" -> 1|>, <|"id" -> 321319, "name" -> "Brazilian Division 2", "eventTypeId" -> 1|>, <| "id" -> 37, "name" -> "League Two", "eventTypeId" -> 1|>, <| "id" -> 868392, "name" -> "Russian Youth League", "eventTypeId" -> 1|>, <|"id" -> 8244827, "name" -> "Denmark Series", "eventTypeId" -> 1|>, <|"id" -> 39, "name" -> "National League", "eventTypeId" -> 1|>, <| "id" -> 8594603, "name" -> "Peruvian Primera Division", "eventTypeId" -> 1|>, <|"id" -> 4905, "name" -> "Liga 1", "eventTypeId" -> 1|>, <|"id" -> 1065530, "name" -> "Venezuelan Primera", "eventTypeId" -> 1|>, <| "id" -> 6908995, "name" -> "Kuwait Premier League", "eventTypeId" -> 1|>, <|"id" -> 2609677, "name" -> "Moroccan Division 1", "eventTypeId" -> 1|>, <| "id" -> 4655, "name" -> "Copa Sudamericana", "eventTypeId" -> 1|>, <|"id" -> 4556663, "name" -> "Algerian U21 Ligue 2", "eventTypeId" -> 1|>, <| "id" -> 7809093, "name" -> "Primera B Metropolitana (Res)", "eventTypeId" -> 1|>, <|"id" -> 4556656, "name" -> "Algerian Ligue 2", "eventTypeId" -> 1|>, <| "id" -> 879931, "name" -> "Chinese Super League", "eventTypeId" -> 1|>, <|"id" -> 55, "name" -> "French Ligue 1", "eventTypeId" -> 1|>, <|"id" -> 4517491, "name" -> "I Divizion", "eventTypeId" -> 1|>, <|"id" -> 57, "name" -> "French Ligue 2", "eventTypeId" -> 1|>, <|"id" -> 67387, "name" -> "Argentinian Primera Division", "eventTypeId" -> 1|>, <| "id" -> 59, "name" -> "Bundesliga 1", "eventTypeId" -> 1|>, <| "id" -> 10150305, "name" -> "A League 2016/17", "eventTypeId" -> 1|>, <|"id" -> 10222240, "name" -> "Sergipano A2", "eventTypeId" -> 1|>, <|"id" -> 61, "name" -> "Bundesliga 2", "eventTypeId" -> 1|>, <|"id" -> 65, "name" -> "Regionalliga Sudwest", "eventTypeId" -> 1|>, <| "id" -> 175680, "name" -> "Turkish Division 1", "eventTypeId" -> 1|>, <|"id" -> 67, "name" -> "Greek Super League", "eventTypeId" -> 1|>, <| "id" -> 961354, "name" -> "Chilean Cup", "eventTypeId" -> 1|>, <| "id" -> 880458, "name" -> "Russian Division 1", "eventTypeId" -> 1|>, <|"id" -> 3792498, "name" -> "Algerian U21 Ligue 1", "eventTypeId" -> 1|>, <| "id" -> 77, "name" -> "Airtricity Premier Division", "eventTypeId" -> 1|>, <|"id" -> 81, "name" -> "Serie A", "eventTypeId" -> 1|>, <|"id" -> 83, "name" -> "Serie B", "eventTypeId" -> 1|>, <|"id" -> 8770515, "name" -> "Mexican Liga Premier", "eventTypeId" -> 1|>, <| "id" -> 2134, "name" -> "EFL Cup", "eventTypeId" -> 1|>, <| "id" -> 89, "name" -> "J League", "eventTypeId" -> 1|>, <| "id" -> 164952, "name" -> "Belgian Second Division", "eventTypeId" -> 1|>, <|"id" -> 9161936, "name" -> "Kyrgyzstan Top Liga", "eventTypeId" -> 1|>, <| "id" -> 862545, "name" -> "Estonian Meistriliiga", "eventTypeId" -> 1|>, <|"id" -> 833616, "name" -> "Greek Football League", "eventTypeId" -> 1|>, <| "id" -> 93, "name" -> "Tippeligaen", "eventTypeId" -> 1|>, <| "id" -> 30558, "name" -> "English FA Cup", "eventTypeId" -> 1|>, <| "id" -> 6569531, "name" -> "Cymru Alliance", "eventTypeId" -> 1|>, <|"id" -> 97, "name" -> "Ekstraklasa", "eventTypeId" -> 1|>, <|"id" -> 99, "name" -> "Primeira Liga", "eventTypeId" -> 1|>, <|"id" -> 4095837, "name" -> "Moroccan Cup", "eventTypeId" -> 1|>, <|"id" -> 840808, "name" -> "Regionalliga West", "eventTypeId" -> 1|>, <| "id" -> 101, "name" -> "Premier Division", "eventTypeId" -> 1|>, <| "id" -> 803690, "name" -> "Ecuadorian Primera A", "eventTypeId" -> 1|>, <|"id" -> 827754, "name" -> "Mexican Liga de Ascenso", "eventTypeId" -> 1|>, <| "id" -> 103, "name" -> "Serbian Super Liga", "eventTypeId" -> 1|>, <|"id" -> 105, "name" -> "Scottish Premiership", "eventTypeId" -> 1|>, <| "id" -> 8771055, "name" -> "El Salvador U20 League", "eventTypeId" -> 1|>, <|"id" -> 107, "name" -> "Scottish Championship", "eventTypeId" -> 1|>, <| "id" -> 7247362, "name" -> "Ugandan Premier League", "eventTypeId" -> 1|>, <|"id" -> 7317506, "name" -> "Austrian Regionalliga", "eventTypeId" -> 1|>, <| "id" -> 7998231, "name" -> "Danish Division 2", "eventTypeId" -> 1|>, <|"id" -> 109, "name" -> "Scottish League One", "eventTypeId" -> 1|>, <| "id" -> 1227903, "name" -> "Copa Argentina", "eventTypeId" -> 1|>, <|"id" -> 111, "name" -> "Scottish League Two", "eventTypeId" -> 1|>, <| "id" -> 113, "name" -> "Slovakian Super Liga", "eventTypeId" -> 1|>, <|"id" -> 7893771, "name" -> "Copa Venezuela", "eventTypeId" -> 1|>, <| "id" -> 5627174, "name" -> "Mexican Primera Division", "eventTypeId" -> 1|>, <|"id" -> 4684340, "name" -> "Friendlies (W)", "eventTypeId" -> 1|>, <|"id" -> 115, "name" -> "Slovenian Prva Liga", "eventTypeId" -> 1|>, <| "id" -> 9969899, "name" -> "Premier League 2", "eventTypeId" -> 1|>, <|"id" -> 1888360, "name" -> "Ukrainian U19 League", "eventTypeId" -> 1|>, <| "id" -> 117, "name" -> "Primera Division", "eventTypeId" -> 1|>, <| "id" -> 119, "name" -> "Segunda Division", "eventTypeId" -> 1|>, <| "id" -> 879221, "name" -> "Lithuanian A Lyga", "eventTypeId" -> 1|>, <|"id" -> 121, "name" -> "Segunda B/1", "eventTypeId" -> 1|>, <|"id" -> 89979, "name" -> "Belgian Jupiler League", "eventTypeId" -> 1|>, <| "id" -> 5618479, "name" -> "Israeli Liga Alef", "eventTypeId" -> 1|>, <|"id" -> 123, "name" -> "Segunda B/2", "eventTypeId" -> 1|>, <|"id" -> 8272130, "name" -> "Goa U20 League", "eventTypeId" -> 1|>, <|"id" -> 125, "name" -> "Segunda B/3", "eventTypeId" -> 1|>, <|"id" -> 862579, "name" -> "Romanian Liga II", "eventTypeId" -> 1|>, <| "id" -> 1038449, "name" -> "Scottish League Cup", "eventTypeId" -> 1|>, <|"id" -> 20351, "name" -> "NIFL Premiership", "eventTypeId" -> 1|>, <|"id" -> 127, "name" -> "Segunda B/4", "eventTypeId" -> 1|>, <|"id" -> 3057583, "name" -> "Ecuadorian Primera B", "eventTypeId" -> 1|>, <| "id" -> 129, "name" -> "Allsvenskan", "eventTypeId" -> 1|>, <| "id" -> 2608550, "name" -> "Specials", "eventTypeId" -> 1|>, <| "id" -> 89219, "name" -> "Brazilian Cup", "eventTypeId" -> 1|>, <| "id" -> 131, "name" -> "Superettan", "eventTypeId" -> 1|>, <| "id" -> 318343, "name" -> "Regionalliga Nordost", "eventTypeId" -> 1|>, <|"id" -> 133, "name" -> "Swiss Super League", "eventTypeId" -> 1|>, <| "id" -> 252549, "name" -> "Welsh Premier League", "eventTypeId" -> 1|>, <|"id" -> 135, "name" -> "Swiss Challenge League", "eventTypeId" -> 1|>, <| "id" -> 887178, "name" -> "Virsliga", "eventTypeId" -> 1|>, <| "id" -> 409743, "name" -> "Scottish Cup", "eventTypeId" -> 1|>, <| "id" -> 139, "name" -> "Vischya Liga", "eventTypeId" -> 1|>, <| "id" -> 403085, "name" -> "Polish 1 Liga", "eventTypeId" -> 1|>, <| "id" -> 1987477, "name" -> "Czech U19 League", "eventTypeId" -> 1|>, <|"id" -> 6251219, "name" -> "Indian Super League", "eventTypeId" -> 1|>, <| "id" -> 4596938, "name" -> "Isthmian Division One South", "eventTypeId" -> 1|>, <|"id" -> 141, "name" -> "MLS", "eventTypeId" -> 1|>, <|"id" -> 867459, "name" -> "K League", "eventTypeId" -> 1|>, <|"id" -> 6366702, "name" -> "Shillong Premier League", "eventTypeId" -> 1|>, <| "id" -> 1979279, "name" -> "Copa Mexico", "eventTypeId" -> 1|>, <| "id" -> 7616742, "name" -> "Kenyan Premier League", "eventTypeId" -> 1|>, <|"id" -> 194961, "name" -> "CONCACAF Champions League", "eventTypeId" -> 1|>, <| "id" -> 876442, "name" -> "Polish 2 Liga", "eventTypeId" -> 1|>, <| "id" -> 1062024, "name" -> "J2 League", "eventTypeId" -> 1|>, <| "id" -> 839575, "name" -> "Paraguayan Primera", "eventTypeId" -> 1|>, <|"id" -> 2311356, "name" -> "AFC U19 Championship", "eventTypeId" -> 1|>, <| "id" -> 156320, "name" -> "North American Soccer League", "eventTypeId" -> 1|>, <|"id" -> 4556519, "name" -> "Palestine West Bank League", "eventTypeId" -> 1|>, <| "id" -> 862638, "name" -> "Czech 2 Liga", "eventTypeId" -> 1|>, <| "id" -> 194215, "name" -> "Turkish Super League", "eventTypeId" -> 1|>, <|"id" -> 3784863, "name" -> "UEFA Youth League", "eventTypeId" -> 1|>, <| "id" -> 844197, "name" -> "Colombian Primera A", "eventTypeId" -> 1|>, <|"id" -> 5984496, "name" -> "Lega Pro", "eventTypeId" -> 1|>, <|"id" -> 4540650, "name" -> "NIFL Reserve League", "eventTypeId" -> 1|>, <| "id" -> 853948, "name" -> "Regionalliga Bayern", "eventTypeId" -> 1|>, <|"id" -> 7550146, "name" -> "National Football League", "eventTypeId" -> 1|>, <| "id" -> 843454, "name" -> "Uruguayan Primera", "eventTypeId" -> 1|>, <|"id" -> 801976, "name" -> "Egyptian Premier", "eventTypeId" -> 1|>, <| "id" -> 840886, "name" -> "Malaysian Super League", "eventTypeId" -> 1|>, <|"id" -> 3765124, "name" -> "Argentinian Primera B Metropolitana", "eventTypeId" -> 1|>, <|"id" -> 158146, "name" -> "3 Liga", "eventTypeId" -> 1|>, <|"id" -> 804044, "name" -> "Israeli Liga Leumit", "eventTypeId" -> 1|>, <| "id" -> 3006957, "name" -> "Romanian Liga III", "eventTypeId" -> 1|>, <|"id" -> 6182297, "name" -> "Gibraltar Premier Division", "eventTypeId" -> 1|>, <| "id" -> 4644737, "name" -> "Northern Premier League Division One South", "eventTypeId" -> 1|>, <|"id" -> 30921, "name" -> "Danish Cup", "eventTypeId" -> 1|>, <|"id" -> 853446, "name" -> "Saudi Premier", "eventTypeId" -> 1|>, <|"id" -> 848322, "name" -> "Singapore S League", "eventTypeId" -> 1|>, <| "id" -> 5614746, "name" -> "FIFA World Cup 2018", "eventTypeId" -> 1|>, <|"id" -> 2005, "name" -> "UEFA Europa League", "eventTypeId" -> 1|>, <| "id" -> 4802204, "name" -> "El Salvadoran Primera Division", "eventTypeId" -> 1|>, <|"id" -> 6812603, "name" -> "Guatemalan Primera Division de Ascenso", "eventTypeId" -> 1|>, <|"id" -> 1476085, "name" -> "Malaysian Premier League", "eventTypeId" -> 1|>, <| "id" -> 228, "name" -> "UEFA Champions League", "eventTypeId" -> 1|>, <|"id" -> 834025, "name" -> "Spanish Copa Federacion", "eventTypeId" -> 1|>, <| "id" -> 3744990, "name" -> "Copa do Brasil U20", "eventTypeId" -> 1|>, <|"id" -> 258537, "name" -> "UAE Premier League", "eventTypeId" -> 1|>, <| "id" -> 1842928, "name" -> "Hungarian NB I", "eventTypeId" -> 1|>, <|"id" -> 7596190, "name" -> "Croatian 3 HNL", "eventTypeId" -> 1|>, <| "id" -> 7129730, "name" -> "The Championship", "eventTypeId" -> 1|>, <|"id" -> 30961, "name" -> "German Cup", "eventTypeId" -> 1|>, <|"id" -> 3729355, "name" -> "Bolivian Liga Nacional A", "eventTypeId" -> 1|>, <| "id" -> 873203, "name" -> "Estonian Esiliiga", "eventTypeId" -> 1|>, <|"id" -> 9554737, "name" -> "ITF Colombia Futures", "eventTypeId" -> 2|>, <| "id" -> 10272700, "name" -> "Barclays ATP World Tour Finals 2016", "eventTypeId" -> 2|>, <|"id" -> 10282617, "name" -> "Santiago Challenger 2016", "eventTypeId" -> 2|>, <| "id" -> 10304603, "name" -> "Tie Break Tens Vienna", "eventTypeId" -> 2|>, <|"id" -> 8479559, "name" -> "Tennis Specials 2016", "eventTypeId" -> 2|>, <| "id" -> 10283675, "name" -> "ITF Suzhou", "eventTypeId" -> 2|>, <| "id" -> 8764909, "name" -> "Fed Cup 2016", "eventTypeId" -> 2|>, <| "id" -> 10282390, "name" -> "If Stockholm Open 2016", "eventTypeId" -> 2|>, <|"id" -> 10284023, "name" -> "ITF Joue-Les-Tours", "eventTypeId" -> 2|>, <| "id" -> 9735288, "name" -> "Wimbledon 2017", "eventTypeId" -> 2|>, <|"id" -> 10282289, "name" -> "European Open 2016", "eventTypeId" -> 2|>, <| "id" -> 10281105, "name" -> "Ningbo Challenger 2016", "eventTypeId" -> 2|>, <|"id" -> 10282705, "name" -> "Las Vegas Challenger 2016", "eventTypeId" -> 2|>, <| "id" -> 8788904, "name" -> "Davis Cup 2016", "eventTypeId" -> 2|>, <|"id" -> 10288748, "name" -> "WTA Tour Finals 2016", "eventTypeId" -> 2|>, <| "id" -> 10281071, "name" -> "VTB Kremlin Cup 2016", "eventTypeId" -> 2|>, <|"id" -> 9556388, "name" -> "French Open 2017", "eventTypeId" -> 2|>, <| "id" -> 10282502, "name" -> "Brest Challenger 2016", "eventTypeId" -> 2|>, <|"id" -> 10094279, "name" -> "US Open 2017", "eventTypeId" -> 2|>, <| "id" -> 10282051, "name" -> "BNP Paribas Luxembourg Open 2016", "eventTypeId" -> 2|>, <|"id" -> 9884918, "name" -> "US PGA 2017", "eventTypeId" -> 3|>, <|"id" -> 10289644, "name" -> "CIMB Classic 2016", "eventTypeId" -> 3|>, <| "id" -> 10272558, "name" -> "Solheim Cup 2017", "eventTypeId" -> 3|>, <|"id" -> 10285407, "name" -> "LPGA BLUE BAY", "eventTypeId" -> 3|>, <| "id" -> 10273912, "name" -> "World Cup Of Golf 2016", "eventTypeId" -> 3|>, <|"id" -> 10187164, "name" -> "Ryder Cup 2018", "eventTypeId" -> 3|>, <| "id" -> 10292826, "name" -> "Portugal Masters 2016", "eventTypeId" -> 3|>, <|"id" -> 9724204, "name" -> "US Open 2017", "eventTypeId" -> 3|>, <|"id" -> 9773869, "name" -> "Open Championship 2017", "eventTypeId" -> 3|>, <| "id" -> 9112880, "name" -> "US Masters 2017", "eventTypeId" -> 3|>, <|"id" -> 8597727, "name" -> "Race To Dubai 2016", "eventTypeId" -> 3|>, <| "id" -> 10148074, "name" -> "Matador BBQs One Day Cup 2016", "eventTypeId" -> 4|>, <|"id" -> 7126733, "name" -> "ICC Cricket World Cup 2019", "eventTypeId" -> 4|>, <| "id" -> 10280907, "name" -> "Test Matches", "eventTypeId" -> 4|>, <|"id" -> 9616981, "name" -> "ICC Champions Trophy 2017", "eventTypeId" -> 4|>, <| "id" -> 9962116, "name" -> "One Day Internationals", "eventTypeId" -> 4|>, <|"id" -> 10157220, "name" -> "Big Bash League 2016/17", "eventTypeId" -> 4|>, <| "id" -> 9886504, "name" -> "Womens One Day Internationals", "eventTypeId" -> 4|>, <|"id" -> 7713877, "name" -> "Champions Cup 2016/17", "eventTypeId" -> 5|>, <| "id" -> 7875241, "name" -> "Aviva Premiership 2016/17", "eventTypeId" -> 5|>, <|"id" -> 8925529, "name" -> "2017 RBS 6 Nations", "eventTypeId" -> 5|>, <| "id" -> 9720006, "name" -> "French Top 14 2016/17", "eventTypeId" -> 5|>, <|"id" -> 9969164, "name" -> "National Rugby Championship 2016", "eventTypeId" -> 5|>, <|"id" -> 9717744, "name" -> "European Challenge Cup 2016/17", "eventTypeId" -> 5|>, <|"id" -> 9868559, "name" -> "Currie Cup 2016", "eventTypeId" -> 5|>, <|"id" -> 8921, "name" -> "Internationals", "eventTypeId" -> 5|>, <| "id" -> 9931418, "name" -> "Mitre 10 Cup 2016", "eventTypeId" -> 5|>, <|"id" -> 8415646, "name" -> "Rugby World Cup 2019", "eventTypeId" -> 5|>, <| "id" -> 5496748, "name" -> "Pro12 2016/17", "eventTypeId" -> 5|>, <|"id" -> 10239542, "name" -> "Super League 2017", "eventTypeId" -> 1477|>, <| "id" -> 10168263, "name" -> "NRL 2017", "eventTypeId" -> 1477|>, <| "id" -> 10188309, "name" -> "State of Origin 2017", "eventTypeId" -> 1477|>, <|"id" -> 215152, "name" -> "November Bouts", "eventTypeId" -> 6|>, <| "id" -> 1979414, "name" -> "October Bouts", "eventTypeId" -> 6|>, <|"id" -> 10301629, "name" -> "Speedway Australian GP", "eventTypeId" -> 8|>, <| "id" -> 8421842, "name" -> "F1 Outrights 2016", "eventTypeId" -> 8|>, <|"id" -> 8581589, "name" -> "Moto GP 2016", "eventTypeId" -> 8|>, <|"id" -> 9084902, "name" -> "Speedway - World Championship 2016", "eventTypeId" -> 8|>, <|"id" -> 10303984, "name" -> "Moto GP Australia", "eventTypeId" -> 8|>, <| "id" -> 10275346, "name" -> "F1 United States GP", "eventTypeId" -> 8|>, <|"id" -> 9454110, "name" -> "DOTA The Summit", "eventTypeId" -> 27454571|>, <| "id" -> 10096701, "name" -> "Scottish International Open 2016.", "eventTypeId" -> 998918|>, <|"id" -> 8958729, "name" -> "BBC Sports Personality 2016", "eventTypeId" -> 10|>, <| "id" -> 8958667, "name" -> "2024 Olympic Host City", "eventTypeId" -> 10|>, <|"id" -> 9476453, "name" -> "Turner Prize 2016", "eventTypeId" -> 10|>, <| "id" -> 9927218, "name" -> "Strictly Come Dancing 2016", "eventTypeId" -> 10|>, <|"id" -> 9872462, "name" -> "Man Booker Prize 2016", "eventTypeId" -> 10|>, <| "id" -> 10156727, "name" -> "Swedish Idol 2016", "eventTypeId" -> 10|>, <|"id" -> 9927225, "name" -> "The X Factor 2016", "eventTypeId" -> 10|>, <| "id" -> 10166523, "name" -> "Italy V SuperLega", "eventTypeId" -> 998917|>, <|"id" -> 10282543, "name" -> "South Korea V V League Women", "eventTypeId" -> 998917|>, <|"id" -> 10282537, "name" -> "South Korea V V League", "eventTypeId" -> 998917|>, <| "id" -> 9231083, "name" -> "Poland V Liga Siatkowki", "eventTypeId" -> 998917|>, <|"id" -> 10280116, "name" -> "V FIVB Club World Championships", "eventTypeId" -> 998917|>, <|"id" -> 10280118, "name" -> "V FIVB Club World Championships Women", "eventTypeId" -> 998917|>, <|"id" -> 9734605, "name" -> "UK National Hill Championship", "eventTypeId" -> 11|>, <|"id" -> 9864939, "name" -> "Tour de France 2017", "eventTypeId" -> 11|>, <| "id" -> 7008771, "name" -> "New Customer Only. First Bet Exclusive 10", "eventTypeId" -> 136332|>, <|"id" -> 7942963, "name" -> "New Customer Only. first Bet Exclusive 12", "eventTypeId" -> 136332|>, <|"id" -> 6964614, "name" -> "New Customer Only. First Bet Exclusive 8", "eventTypeId" -> 136332|>, <|"id" -> 7942967, "name" -> "New Customer Only. first Bet Exclusive 13", "eventTypeId" -> 136332|>, <|"id" -> 6918343, "name" -> "New Customer Only. First Bet Exclusive 2", "eventTypeId" -> 136332|>, <|"id" -> 6961573, "name" -> "New Customer Only. First Bet Exclusive 4", "eventTypeId" -> 136332|>, <|"id" -> 6694857, "name" -> "New Customer Only. First Bet Exclusive 3", "eventTypeId" -> 136332|>, <|"id" -> 6964600, "name" -> "New Customer Only. First Bet Exclusive 6", "eventTypeId" -> 136332|>, <|"id" -> 7942955, "name" -> "New Customer Only. first Bet Exclusive 14", "eventTypeId" -> 136332|>, <|"id" -> 9135832, "name" -> "2016 World Chess Championships", "eventTypeId" -> 136332|>, <|"id" -> 6964607, "name" -> "New Customer Only. First Bet Exclusive 7", "eventTypeId" -> 136332|>, <|"id" -> 6961599, "name" -> "New Customer Only. First Bet Exclusive 1", "eventTypeId" -> 136332|>, <|"id" -> 7008764, "name" -> "New Customer Only. First Bet Exclusive 9", "eventTypeId" -> 136332|>, <|"id" -> 7942977, "name" -> "New Customer Only. First Bet Exclusive 15", "eventTypeId" -> 136332|>, <|"id" -> 6961588, "name" -> "New Customer Only. First Bet Exclusive 5", "eventTypeId" -> 136332|>, <|"id" -> 9157639, "name" -> "WSOP 2016", "eventTypeId" -> 315220|>, <| "id" -> 9993990, "name" -> "Tokyo 2020", "eventTypeId" -> 3988|>, <|"id" -> 9233037, "name" -> "World Championship 2017", "eventTypeId" -> 6422|>, <| "id" -> 8707741, "name" -> "The Masters 2017", "eventTypeId" -> 6422|>, <|"id" -> 8568745, "name" -> "UK Championship 2016", "eventTypeId" -> 6422|>, <| "id" -> 9964393, "name" -> "Australian Interest Rates", "eventTypeId" -> 6231|>, <|"id" -> 2769078, "name" -> "UK Interest Rates", "eventTypeId" -> 6231|>, <| "id" -> 8962390, "name" -> "USA Interest Rates", "eventTypeId" -> 6231|>, <|"id" -> 8788754, "name" -> "CFL 2016", "eventTypeId" -> 6423|>, <|"id" -> 8788798, "name" -> "NCAAF 2016/17", "eventTypeId" -> 6423|>, <| "id" -> 8764204, "name" -> "NFL Season 2016/17", "eventTypeId" -> 6423|>, <|"id" -> 6359473, "name" -> "MLB 2016", "eventTypeId" -> 7511|>, <|"id" -> 10303773, "name" -> "UFC 207", "eventTypeId" -> 27829360|>, <|"id" -> 10280536, "name" -> "UFC 205", "eventTypeId" -> 27829360|>, <| "id" -> 10280610, "name" -> "UFC Fight Night Mexico City", "eventTypeId" -> 27829360|>, <|"id" -> 10169167, "name" -> "Constellation Cup 2016", "eventTypeId" -> 606611|>, <| "id" -> 9191429, "name" -> "Alpine Skiing World Cup 2016/17", "eventTypeId" -> 451485|>, <|"id" -> 10220826, "name" -> "Extraliga 2016 - 2017", "eventTypeId" -> 7522|>, <| "id" -> 9318860, "name" -> "Liga Endesa", "eventTypeId" -> 7522|>, <|"id" -> 9310282, "name" -> "NBA", "eventTypeId" -> 7522|>, <|"id" -> 9353227, "name" -> "Dominent Bank Ekstraliga", "eventTypeId" -> 7522|>, <| "id" -> 10273173, "name" -> "Bulgarian Basketball League 2016 2017", "eventTypeId" -> 7522|>, <|"id" -> 10152915, "name" -> "Japan B1 League", "eventTypeId" -> 7522|>, <| "id" -> 9319111, "name" -> "Ligat HaAl", "eventTypeId" -> 7522|>, <|"id" -> 10157073, "name" -> "NBA Preseason", "eventTypeId" -> 7522|>, <| "id" -> 10139537, "name" -> "Mattoni NBL 2016 - 2017", "eventTypeId" -> 7522|>, <|"id" -> 10137367, "name" -> "British Basketball League 2016 - 2017", "eventTypeId" -> 7522|>, <|"id" -> 10169429, "name" -> "Basketball League of Serbia 2016 2017", "eventTypeId" -> 7522|>, <|"id" -> 10224910, "name" -> "ULEB Eurocup 2016 - 2017", "eventTypeId" -> 7522|>, <| "id" -> 10273609, "name" -> "Championnat Pro B 2016- 2016 2017", "eventTypeId" -> 7522|>, <|"id" -> 10121859, "name" -> "Championnat Pro A 2016 2017", "eventTypeId" -> 7522|>, <|"id" -> 10218496, "name" -> "WNBL 2016 2017", "eventTypeId" -> 7522|>, <| "id" -> 10161600, "name" -> "Basketball Champions League", "eventTypeId" -> 7522|>, <|"id" -> 10283584, "name" -> "FIBA Europe Cup", "eventTypeId" -> 7522|>, <| "id" -> 9317712, "name" -> "Korisliiga", "eventTypeId" -> 7522|>, <|"id" -> 10118212, "name" -> "Liga Nacional de Básquetbol 2016 2017", "eventTypeId" -> 7522|>, <|"id" -> 9319212, "name" -> "Turkish Airlines Euroleague Basketball", "eventTypeId" -> 7522|>, <|"id" -> 9339372, "name" -> "Basketball Bundesliga", "eventTypeId" -> 7522|>, <| "id" -> 9338605, "name" -> "WNBA 2016", "eventTypeId" -> 7522|>, <| "id" -> 10153726, "name" -> "ABA Liga 2016 - 2017", "eventTypeId" -> 7522|>, <|"id" -> 10157361, "name" -> "Australian NBL 2016/17", "eventTypeId" -> 7522|>, <| "id" -> 10161710, "name" -> "Canal Digital Ligaen 2016 2017", "eventTypeId" -> 7522|>, <|"id" -> 10187180, "name" -> "Ligan", "eventTypeId" -> 7522|>, <|"id" -> 8610214, "name" -> "Finland IH Mestis", "eventTypeId" -> 7524|>, <| "id" -> 10115932, "name" -> "Sweden IH SHL", "eventTypeId" -> 7524|>}]

Answer 1

Provided that your Datasets are effectively just a list of Associations then you should use JoinAcross, as follows:

dsCompetitions = Dataset@{<|"id" -> 1, "name" -> "bob", "eventTypeId" -> 4|>, 
   <|"id" -> 2, "name" -> "frank", "eventTypeId" -> 6|>};
dsEventTypes = Dataset@{<|"id" -> 4, "name" -> "Football"|>, 
   <|"id" -> 5, "name" -> "Baseball"|>, 
   <|"id" -> 6, "name" -> "American Football"|>};

Normal@JoinAcross[dsCompetitions, dsEventTypes, 
   {"eventTypeId" -> "id"}, 
  KeyCollisionFunction -> <|"id" -> {"id", "eventId"}, 
    "name" -> {"compName", "eventName"}|>][All, {"id", "compName", "eventName"}]
(* {<|"id" -> 1, "compName" -> "bob", "eventName" -> "Football"|>, 
  <|"id" -> 2, "compName" -> "frank", "eventName" -> "American Football"|>} *)

where Normal has been added to simplify the output for SE. The third argument of JoinAcross is where you specify the two keys to join. The option KeyCollisionFunction specifies which data is to take precedence when there are key collisions among the non-joined fields. But, in this case, it is better to use the function form which provides rewrite rules for the key names to prevent conflicts.