BIRT SQL Null案例场景

Question

我使用以下SQL语句检索有关工作顺序的各种信息。但是，其中一些没有列出资产(NULL)，在这种情况下，我将得到0行：

select PM17, WORKORDER.PMNUM, WORKORDER.ASSETNUM, ASSET.DESCRIPTION, ASSET.MANUFACTURER, EQASSETNUM, WORKORDER.LOCATION, LOCATIONS.DESCRIPTION, PM.ROUTE, TARGSTARTDATE, TARGCOMPDATE, PM.NEXTDATE
from WORKORDER 
JOIN ASSET ON WORKORDER.ASSETNUM = ASSET.ASSETNUM
JOIN PM ON WORKORDER.PMNUM = PM.PMNUM
JOIN LOCATIONS ON WORKORDER.LOCATION = LOCATIONS.LOCATION
WHERE WORKORDER.WONUM='1011';

我如何修改我的声明来处理这两种情况(有或没有ASSETNUM)？

案例1 (ASSETNUM != NULL)->显示选定的所有字段

案例2 (ASSETNUM = NULL) ->显示不依赖于ASSETNUM的字段，其余字段返回null

Answer 1

将JOIN更改为LEFT JOIN

select PM17, WORKORDER.PMNUM, WORKORDER.ASSETNUM, ASSET.DESCRIPTION, ASSET.MANUFACTURER, EQASSETNUM, WORKORDER.LOCATION, LOCATIONS.DESCRIPTION, PM.ROUTE, TARGSTARTDATE, TARGCOMPDATE, PM.NEXTDATE
from WORKORDER 
LEFT JOIN ASSET ON WORKORDER.ASSETNUM = ASSET.ASSETNUM
JOIN PM ON WORKORDER.PMNUM = PM.PMNUM
JOIN LOCATIONS ON WORKORDER.LOCATION = LOCATIONS.LOCATION
WHERE WORKORDER.WONUM='1011';