如何编写SQL相关的子查询

Question

我有两个表 itemsDetails 和 itemssssquare

**ItemsDetails** table have following column
ItmDtlId P.K
Itmid F.K
AssetId F.k
Qty 
TDate
Approved
**ItemsSquare** table have following column
ItmSqrId P.k
ItmDtlId F.k
ItmSqQty
Date

来自 itemsDetails 表我想显示 itemDetails 表的所有记录，也来自 itemssssquare 表的记录ITMDTLID与> ItemDetails 中的ITMDTLID相同，然后将ItemDetails表和itemquare表的数量作为qty> itmsqqty

进行比较

基本上，我想访问 itemsssquare 表的列 itemDetails 基于表的o ItmdtiD因为第一桌没有第二个表的关系

我正在使用SQL相关的子查询，但我没有得到预期的结果

这是我的SQL查询

SELECT itd.ItmDtlId
     , it.Itmid
     , itd.Qty
     , itd.Approved
     , as.Assetid
     , as.Assetname
     , itd.TDate 
  from ItemsDetails itd
  join Item it 
    on itd.Itmid = it.Itmid
  join Assets as 
    on itd.Assetsid = as.Assetsid
 WHERE itd.Approved = 1 
   and itd.ItmDtlId = (SELECT itd.ItmDtlId FROM ItemsSquare its WHERE itd.ItmDtlId = its.ItmDtlIdand itd.Qty > ItmSqQty) 

请建议我如何有效地编写SQL子查询以获得所需的结果

Answer 1

您可能错误地引用了子查询吗？

而不是：

...
AND itd.ItmDtlId = (
    SELECT itd.ItmDtlId 
    FROM ItemsSquare its
    WHERE itd.ItmDtlId = its.ItmDtlIdand
      AND itd.Qty > its.ItmSqQty
)

to：

...
AND itd.ItmDtlId = (
    SELECT its.ItmDtlId 
    FROM ItemsSquare its
    WHERE itd.ItmDtlId = its.ItmDtlIdand
      AND itd.Qty > its.ItmSqQty
)