带联接的查询需要"重写"以包含不在详细信息级别文件中的记录

Question

我有一个查询，它排除了第一个联接中没有匹配项的记录。下面只给出在Driver_Indents中匹配的Driver_Trend记录，尽管我需要它包括在Driver_Intents中找不到的记录。

SELECT A.DRIVER_NAME AS DRIVER_NAME, A.DRIVER_ID AS DRIVER_ID, C.TRC_TERMINAL AS CSC, A.OBSERVATIONS AS OBSERVATIONS, 
A.INCIDENTS AS INCIDENTS, B.SPEED_LIMIT AS SPEED_LIMIT, B.SPEED AS SPEED, B.DIFFERENCE AS DIFFERENCE,     
A.REPORT_DATE AS REPORT_DATE, B.TIME AS TIME,    

CASE WHEN DIFFERENCE >= 6 AND DIFFERENCE <= 10 THEN '1' ELSE '0' END AS SIX_TEN_MPH,
CASE WHEN DIFFERENCE > 10 AND DIFFERENCE <= 15 THEN '1' ELSE '0' END AS ELEVEN_FIFTEEN_MPH,
CASE WHEN DIFFERENCE > 15 THEN '1' ELSE '0' END AS SIXTEEN_PLUS_MPH

FROM "PROD"."PUBLIC"."SG_DRIVER_TREND" A
LEFT JOIN "PROD"."PUBLIC"."SG_DRIVER_INCIDENTS" B
ON A.DRIVER_ID = B.DRIVER_ID
LEFT JOIN "PROD"."PUBLIC"."TMW_TRACTORPROFILE" C
ON B.Vehicle = C.TRC_NUMBER
WHERE A.DRIVER_ID != ''  
AND A.REPORT_DATE BETWEEN '2022-07-01' AND '2022-07-31'
AND B.TIME BETWEEN '2022-07-01' AND '2022-07-31'
AND SUBSTRING(B.TIME, 0, 10) <= A.REPORT_DATE                                          -- Less than or equal to report date
AND SUBSTRING(B.TIME, 0, 10) > SUBSTRING(DATEADD(week,-1, A.REPORT_DATE), 0, 10)       -- Greater than 1 week ago

我尝试了所有不同类型的加入，但仍然没有成功。是否可以重写此查询以获得Subselect或其他查询所需的内容？代码示例将不胜感激。如果重要的话，这是在Snowflake。

Answer 1

引用外部表的WHERE子句中的条件使查询有效地作为INNER JOIN工作，并且应该移动到ON类：

SELECT A.DRIVER_NAME AS DRIVER_NAME, A.DRIVER_ID AS DRIVER_ID, C.TRC_TERMINAL AS CSC, A.OBSERVATIONS AS OBSERVATIONS, 
A.INCIDENTS AS INCIDENTS, B.SPEED_LIMIT AS SPEED_LIMIT, B.SPEED AS SPEED, B.DIFFERENCE AS DIFFERENCE,     
A.REPORT_DATE AS REPORT_DATE, B.TIME AS TIME,    

CASE WHEN DIFFERENCE >= 6 AND DIFFERENCE <= 10 THEN '1' ELSE '0' END AS SIX_TEN_MPH,
CASE WHEN DIFFERENCE > 10 AND DIFFERENCE <= 15 THEN '1' ELSE '0' END AS ELEVEN_FIFTEEN_MPH,
CASE WHEN DIFFERENCE > 15 THEN '1' ELSE '0' END AS SIXTEEN_PLUS_MPH

FROM "PROD"."PUBLIC"."SG_DRIVER_TREND" A
LEFT JOIN "PROD"."PUBLIC"."SG_DRIVER_INCIDENTS" B
ON A.DRIVER_ID = B.DRIVER_ID
AND B.TIME BETWEEN '2022-07-01' AND '2022-07-31'
AND SUBSTRING(B.TIME, 0, 10) <= A.REPORT_DATE                                          -- Less than or equal to report date
AND SUBSTRING(B.TIME, 0, 10) > SUBSTRING(DATEADD(week,-1, A.REPORT_DATE), 0, 10)     
LEFT JOIN "PROD"."PUBLIC"."TMW_TRACTORPROFILE" C
ON B.Vehicle = C.TRC_NUMBER
WHERE A.DRIVER_ID != ''  
AND A.REPORT_DATE BETWEEN '2022-07-01' AND '2022-07-31';