Skip to content
Advertisement

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

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

Advertisement

Answer

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

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement