我在主要详细信息布局中有两个表。我想仅在主表和明细记录满足条件的情况下才选择它们。但是我希望所有主表项目都没有ID记录是否存在。
我的表格如下：主-库存物品
icmasterid icdescription
WAD110795 WM KIWI-KLIP 3 BEND
WAD110796 WM KIWI-KLIP 3 BEND (MZ3,4,&6)
WAD118806-50 STROLLER VALENCE BACKDRP HINGE
WAD118808 IK STROL B DROP MOUNTING HDW
WAD118942 1" S-HOOK BAG 100
详细信息-库存线
icmasterid icdetailquantity pomasterid
WAD110796 -900 NULL
WAD110796 0 NULL
WAD110796 0 119450
WAD110796 900 119347
WAD118808 0 NULL
WAD118808 34 NULL
WAD118942 0 NULL
WAD118942 59 NULL
WAD118942 0 NULL
WAD118942 -59 NULL
WAD118942 59 NULL
我的SQL
SELECT inventoryitem.icmasterid,
inventoryitem.icdescription,
inventoryline.icdetailquantity,inventoryline.pomasterid
FROM inventoryitem
LEFT OUTER JOIN inventoryline ON inventoryitem.icmasterid=inventoryline.icmasterid
WHERE inventoryitem.icmasterid < 'WAD18' and inventoryitem.icmasterid like 'WAD%'
ORDER BY inventoryitem.icmasterid
上面给出了清单项目中的所有项目以及它们与预期相关的清单行记录。
但是当我添加
and inventoryline.pomasterid <> ''
我只有两行。
icmasterid ICdescription icdetailquantity pomasterid
WAD110796 WM KIWI-KLIP 3 BEND (MZ3,4,&6) 0 119450
WAD110796 WM KIWI-KLIP 3 BEND (MZ3,4,&6) 900 119347
我需要的是
icmasterid icdescription icdetailquantity pomasterid
WAD110796 WM KIWI-KLIP 3 BEND (MZ3,4,&6) 0 119450
WAD110796 WM KIWI-KLIP 3 BEND (MZ3,4,&6) 900 119347
WAD118806-50 STROLLER VALENCE BACKDRP HINGE NULL NULL
WAD118808 IK STROL B DROP MOUNTING HDW NULL NULL
WAD118942 1" S-HOOK BAG 100 NULL NULL
根据蒂姆·比格莱森（Tim Biegeleisen）的回答，我想到了这一点。
SELECT
ii.icmasterid,
ii.icdescription,
ii.icdetailquantity,inventoryline.pomasterid
FROM inventoryitem ii
LEFT JOIN inventoryline il
ON ii.icmasterid = il.icmasterid AND and inventoryline.pomasterid <> ''
WHERE
ii.icmasterid < 'WAD18' AND
ii.icmasterid LIKE 'WAD%'
ORDER BY
ii.icmasterid;
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句