Forum Discussion

MSBuddy25's avatar
MSBuddy25
Icon for Microsoft rankMicrosoft
Mar 04, 2021

Join multiple tables

                       TableA     TableB       TableC  
GUID_PK Name Desc   GUID_FK PriLoc SecLoc   GUID_FK IsPriLoc IsSecLoc
1 Test1 Desc1   1 PriLoc1 SecLoc1   2 TRUE FALSE
2 Test2 Desc2   3 PriLoc3 SecLoc3   4 FALSE TRUE
3 Test3 Desc3   5 PriLoc5 SecLoc5   6 TRUE FALSE
4 Test4 Desc4                
5 Test5 Desc5                
6 Test6 Desc6                

 

After joining the tables: TableA, TableB, TableC using Kusto Query how to show the value of column: IsPriLoc in the column: PriLoc and IsSecLoc in SecLoc. Below is the expected result

 

                       Result      
GUID_PK Name Desc PriLoc SecLoc
1 Test1 Desc1 PriLoc1 SecLoc1
2 Test2 Desc2 PriLoc3 SecLoc3
3 Test3 Desc3 PriLoc5 SecLoc5
4 Test4 Desc4 TRUE FALSE
5 Test5 Desc5 FALSE TRUE
6 Test6 Desc6 TRUE FALSE

2 Replies

    • JLospinoso's avatar
      JLospinoso
      Copper Contributor
      Jul 29, 2021
      In this example, the tables are joined on the commonly named field “UserPrincipalName”, both tables have an “Id” field but the values are not the same. You have to use the “project” syntax to de-conflict and distinguish the left and right values by using “project” for each table.

      SigninLogs
      | project UserPrincipalName, IdLeft = Id
      | join (Anomalies
      | project UserPrincipalName,IdRight = Id)
      on $left.UserPrincipalName == $right.UserPrincipalName
      | project UserPrincipalName, IdRight