工作查询的略有更改导致加入错误

Question

这让我整天挠头。我有多个查询，尝试使用计算的字段加入。第一个查询按预期运行，除了MS Access Design视图不起作用，这没什么大不了的，因为我仍然可以在SQL View中进行编辑。

SELECT PM_qryBOM_1.parentid AS LVL0,
       PM_qryBOM_1.childid AS LVL1,
       [PM_qryBOM_1].[parent_part_no] & "/" & [PM_qryBOM_1].[child_part_no] AS BOM_Address,
       PM_qryBOM_1.qty,
       [PM_qryBOM_1].[parentid] & "/" & [PM_qryBOM_1].[childid] AS BOM_ID,
       sol_avgcost.avgcost_material,
       sol_avgcost.avgcost_labor,
       sol_avgcost.avgcost_sub,
       cm_qrypndj_avg.avgofunit_hrs,
       PM_qryBOM_1.parentid  AS Parent_BOM_ID,
       PM_qryBOM_1.parent_part_no AS Parent_BOM_Address,
       pm_qrybom_lvl1_costroll.mult
FROM   (((pm_qrybom AS PM_qryBOM_1
          LEFT JOIN pm_qrybom AS PM_qryBOM_t
                 ON PM_qryBOM_1.parentid = PM_qryBOM_t.childid)
         LEFT JOIN sol_avgcost
                ON PM_qryBOM_1.child_part_no = sol_avgcost.part_no)
        LEFT JOIN cm_qrypndj_avg
               ON PM_qryBOM_1.child_part_no = cm_qrypndj_avg.part_no)
       LEFT JOIN pm_qrybom_lvl1_costroll
              ON [PM_qryBOM_1].[parentid] & "/" & [PM_qryBOM_1].[childid] =
                 pm_qrybom_lvl1_costroll.parent_bom_id
WHERE  (( ( PM_qryBOM_t.childid ) IS NULL ))
ORDER  BY [PM_qryBOM_1].[parent_part_no] & "/" & [PM_qryBOM_1].[child_part_no];

我还有其他多个查询，我想以这种方式 JOIN。因此，我尝试将同样的语法应用于我的下一个查询，但是我会收到错误

不支持加入表达式。

 SELECT pm_qrybom_lvl0.lvl0,
       pm_qrybom.childid AS LVL1,
       pm_qrybom.qty,
       [pm_qrybom_lvl0].[bom_address] & "/" & [pm_qrybom].[child_part_no] AS BOM_Address,
       [pm_qrybom_lvl0].[bom_id] & "/" & [pm_qrybom].[childid] AS BOM_ID,
       pm_qrybom_lvl0.bom_address AS Parent_BOM_Address,
       pm_qrybom_lvl0.bom_id AS Parent_BOM_ID,
       sol_avgcost.avgcost_material,
       cm_qrypndj_avg.avgofunit_hrs,
       cm_collections.cpq_material,
       pm_qrybom_lvl1_costroll.mult,
       Iif([cm_collections].[cpq_material] <> 0, [cm_collections].[cpq_material], 
           Iif([mult] = 0, [sol_avgcost].[avgcost_material], [mult])) AS Material_Calc
FROM   ((((pm_qrybom_lvl0
           INNER JOIN pm_qrybom
                   ON pm_qrybom_lvl0.lvl0 = pm_qrybom.parentid)
          LEFT JOIN sol_avgcost
                 ON pm_qrybom.child_part_no = sol_avgcost.part_no)
         LEFT JOIN cm_qrypndj_avg
                ON pm_qrybom.child_part_no = cm_qrypndj_avg.part_no)
        LEFT JOIN cm_collections
               ON pm_qrybom.childid = cm_collections.partid)
       LEFT JOIN pm_qrybom_lvl1_costroll
              ON [pm_qrybom_lvl0].[bom_id] & "/" & [pm_qrybom].[childid] =
                 pm_qrybom_lvl1_costroll.parent_bom_id
ORDER  BY [pm_qrybom_lvl0].[bom_address] & "/" & [pm_qrybom].[child_part_no];  

两个查询的语法都是相同的。只有参考表名称和字段名称不同。那么，为什么我可以在一个查询中加入表达式，而不是下一个查询呢？我搜索了这个问题，有些人建议在JOIN语句中的ON之后将所有内容放在所有内容上。我尝试过，但没有解决这个问题。

我绝对被困在这个方面时，任何建议都将不胜感激。

Answer 1

这是加入语句末尾的串联，与您尝试使用的内部联接的效果不佳。尝试使用左联接。然后，在Where语句中，通过排除nulls来删除右侧没有匹配的语句：

SELECT pm_qrybom_lvl0.lvl0,
       pm_qrybom.childid AS LVL1,
       pm_qrybom.qty,
       [pm_qrybom_lvl0].[bom_address] & "/" & [pm_qrybom].[child_part_no] AS BOM_Address,
       [pm_qrybom_lvl0].[bom_id] & "/" & [pm_qrybom].[childid] AS BOM_ID,
       pm_qrybom_lvl0.bom_address AS Parent_BOM_Address,
       pm_qrybom_lvl0.bom_id AS Parent_BOM_ID,
       sol_avgcost.avgcost_material,
       cm_qrypndj_avg.avgofunit_hrs,
       cm_collections.cpq_material,
       pm_qrybom_lvl1_costroll.mult,
       Iif([cm_collections].[cpq_material] <> 0, [cm_collections].[cpq_material], 
           Iif([mult] = 0, [sol_avgcost].[avgcost_material], [mult])) AS Material_Calc
FROM   ((((pm_qrybom_lvl0
           LEFT JOIN pm_qrybom
                   ON pm_qrybom_lvl0.lvl0 = pm_qrybom.parentid)
          LEFT JOIN sol_avgcost
                 ON pm_qrybom.child_part_no = sol_avgcost.part_no)
         LEFT JOIN cm_qrypndj_avg
                ON pm_qrybom.child_part_no = cm_qrypndj_avg.part_no)
        LEFT JOIN cm_collections
               ON pm_qrybom.childid = cm_collections.partid)
       LEFT JOIN pm_qrybom_lvl1_costroll
              ON [pm_qrybom_lvl0].[bom_id] & "/" & [pm_qrybom].[childid] =
                 pm_qrybom_lvl1_costroll.parent_bom_id
       WHERE pm_qrybom.parentid IS NOT NULL
ORDER  BY [pm_qrybom_lvl0].[bom_address] & "/" & [pm_qrybom].[child_part_no];

如果那不能为您提供所需的结果，您也可以尝试嵌套一个选择语句，以使您在来自语句中的串联。

类似：

SELECT iq.lvl0,
       iq.childid AS LVL1,
       iq.qty,
       iq.BOM_Address,
       iq.BOM_ID,
       iq.Parent_BOM_Address,
       iq.Parent_BOM_ID,
       sol_avgcost.avgcost_material,
       cm_qrypndj_avg.avgofunit_hrs,
       cm_collections.cpq_material,
       pm_qrybom_lvl1_costroll.mult,
       Iif([cm_collections].[cpq_material] <> 0, [cm_collections].[cpq_material], 
           Iif([mult] = 0, [sol_avgcost].[avgcost_material], [mult])) AS Material_Calc
FROM ((((SELECT pm_qrybom_lvl0.lvl0, pm_qrybom.childid, pm_qrybom.qty, 
        [pm_qrybom_lvl0].[bom_address] & "/" & [pm_qrybom].[child_part_no] AS BOM_Address,
        [pm_qrybom_lvl0].[bom_id] & "/" & [pm_qrybom].[childid] AS bom_id,
        pm_qrybom_lvl0.bom_address AS Parent_BOM_Address,
        pm_qrybom_lvl0.bom_id AS Parent_BOM_ID
        FROM pm_qrybom_lvl0 LEFT JOIN pm_qrybom
                   ON pm_qrybom_lvl0.lvl0 = pm_qrybom.parentid
        WHERE pm_qrybom.parentid IS NOT NULL
            ) AS iq
          LEFT JOIN sol_avgcost
                 ON iq.child_part_no = sol_avgcost.part_no)
         LEFT JOIN cm_qrypndj_avg
                ON iq.child_part_no = cm_qrypndj_avg.part_no)
        LEFT JOIN cm_collections
               ON iq.childid = cm_collections.partid)
       LEFT JOIN pm_qrybom_lvl1_costroll
              ON iq.bom_id =
                 pm_qrybom_lvl1_costroll.parent_bom_id

祝你好运。