How to use get value from multiple tables using left join?-CodePudding

I have these 3 Tables. Now I need to show the timezone name from the timezone table in the Devices Table by using left join. How can i do that? Please Help

Table Name : [pbi].[GEOTAB_INFO_GROUPS]

GROUP_ID| GROUP_NAME |LOCATION_ID |IMPORTDATE
b27FA   | 55310  |   55310   |   20220328
b282B   | 35824  |  35824    |   20220328
b2797   | 55876  |   55876    |  20220328

Table Name : [adl].[GEOTAB_VEHICLE_INFO_DEVICES]

Id  Name    SerialNumber    ActiveFrom  ActiveTo    DeviceType  DevicePlans VehicleIdentificationNumber LicensePlate    LicenseState    WorkTime    ProductId   HardwareId  TimeZoneId  Group_Id    Comment ImportDate  ImportTime

b957    09-101-57218  3G    000-000-0000    2017-07-05 23:59:52 2022-01-20 20:24:28 OldGeotab   ProPlus 15GGD271991176896   7063-OP PR  Standard Hours  0   NULL    America/New_York    b2A06   NULL    20220330    2022-03-30 7:00:58

Table Name : [pbi].[Location_Time_Zone]

LocationNumber  TimeZone    ImportDate  ImportTime
301194          Central 20211007   07-10-2021 18:44:57

I Am trying like this but not getting the correct result

select distinct d.Id,g.LOCATION_ID,d.Name,d.SerialNumber,d.ActiveFrom,d.ActiveTo,d.DeviceType,d.DevicePlans,d.VehicleIdentificationNumber,d.LicensePlate,d.LicenseState,d.WorkTime,d.ProductId,d.HardwareId, d.TimeZoneId,tz.TimeZone,d.Groups,d.Comment,d.ImportDate from [adl].[GEOTAB_VEHICLE_INFO_DEVICES] d left join [pbi].[GEOTAB_INFO_GROUPS] g on d.Groups = g.GROUP_ID left join [pbi].[Location_Time_Zone] tz on tz.LocationNumber = g.LOCATION_ID

CodePudding user response：

Select d.*, t.TimeZone 
From [adl].[GEOTAB_VEHICLE_INFO_DEVICES] d
Left Join [pbi].[GEOTAB_INFO_GROUPS] g ON g.Group_Id= d.Group_Id
Left Join [pbi].[Location_Time_Zone] t ON g.Location_Id = t.LocationNumber

CodePudding user response：

In SAP ABAP we use this code to use left join :

tables : GEOTAB_VEHICLE_INFO_DEVICES, GEOTAB_VEHICLE_INFO_DEVICES.

select a~GROUP_ID a~GROUP_NAME a~LOCATION_ID a~IMPORTDATE a~b27FA a~55310 a~55310 a~20220328 a~b282B a~35824 a~35824 a~20220328 a~b2797 a~55876 a~55876 a~20220328 a~b2798 a~55888 a~55888 a~20220328 a~b2812 a~55497 a~55497 a~20220328 a~b27A0 a~55475 a~55475 a~20220328 a~b27B3 a~57233 a~57233 a~20220328 a~b27EC a~55616 a~55616 a~20220328 from table GEOTAB_VEHICLE_INFO_DEVICES as a using left outer join b~Id b~Name b~SerialNumber b~ActiveFrom b~ActiveTo b~DeviceType b~DevicePlans b~VehicleIdentificationNumber b~LicensePlate b~LicenseState b~WorkTime b~ProductId b~HardwareId b~TimeZoneId b~Group_Id b~Comment b~ImportDate b~ImportTime from table GEOTAB_VEHICLE_INFO_DEVICES as B on a~GroupId eq b~GroupID using left outer join c~b957 c~09-101-57218 c~3G c~000-000-0000 c~2017-07-05 c~23:59:52 c~2022-01-20 c~20:24:28 c~OldGeotab c~ProPlus c~15GGD271991176896 c~7063-OP c~PR c~Standard c~Hours c~0 c~NULL c~America/New_York c~b2A06 c~NULL c~20220330 c~2022-03-30 c~7:00:58 from table Location_Time_Zone as c on b~TimeZoneId eq c~TimeZone.