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