Join问题
Join问题
提问于 2019-06-27 07:07:02
我在主细节布局中有两个表。我只想从主表和详细记录中选择所有符合条件的激活项目。但我想要所有的主表项目，无论id是否存在详细记录。
我的表看起来像: Master - inventoryItem
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 详细信息- inventoryLine
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上面的代码如期给出了inventoryitem中的所有项目及其相关的inventoryline记录。
但当我添加
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回答 2
Stack Overflow用户
回答已采纳
发布于 2019-06-27 07:50:45
根据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;Stack Overflow用户
发布于 2019-06-27 07:09:48
将WHERE子句中的逻辑移动到ON子句：
SELECT
ii.icmasterid,
ii.icdescription,
ii.icdetailquantity,inventoryline.pomasterid
FROM inventoryitem ii
LEFT JOIN inventoryline il
ON ii.icmasterid = il.icmasterid AND
-- SUBSTRING(ii.icmasterid, 4, 2) < '18' AND
ii.icmasterid LIKE 'WAD%'
ORDER BY
ii.icmasterid;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56781977
复制相关文章
相似问题