sql - MS Access Join 子查询

Question

我在 Table2 中有数据，我需要将其用作检索 Table1 中信息的键。表 2 包含有关零件编号的信息，包括其制造工厂和生产过程。我需要将仓库（工厂）和零件号传递给 Table2，然后将其传回给我，生产工厂 ID 和流程 ID。我已经尝试使用下面的 SQL，但它会引发一个语法错误，只是说“JOIN 操作中的语法错误”。我宁愿不必拆分成单独的 sql 语句：/

SELECT 
    T1.MFRFMR03 AS Seq, 
    T1.MFRFMR04 AS [Desc], 
    T1.MFRFMR0S AS M2M, 
    T1.MFRFMR0Q AS Std_Labor, 
    T1.MFRFMR0M AS Std_Setup, 
    T1.MFRFMR0R AS Std_Units 
FROM 
    T1 LEFT JOIN 
                        (
                            SELECT 
                                T2.MAJRTEPLT, 
                                T2.MAJRTEID 
                            FROM 
                                T2 
                            WHERE 
                                (
                                    ((T2.PLT)=[Enter Plant Number]) 
                                    AND 
                                    ((T2.ITMID)=ucase$([Enter Part Number]))
                                )
                        ) ON ((T1.MFRFMR01)=(T2.MAJRTEPLT)) AND ((T1.MFRFMR02)=(T2.MAJRTEID))
WHERE 
    (
        ((T1.MFRFMR0Q)<>0) 
        AND 
        ((T1.MFRFMR0I)<>'S') 
        AND 
        ((T1.MFRFMR0G)=0) 
        OR 
        ((T1.MFRFMR0G)=99999) 
        AND
        (
            ((T1.MFRFMR01)=(T2.MAJRTEPLT)) 
            AND 
            ((T1.MFRFMR02)=(T2.MAJRTEID))
            EXISTS IN 
        )
    )   
ORDER BY 
    T1.MFRFMR03;

标签： sqlms-accessms-access-2016

Answer 1

这是因为您的子查询没有别名。您在子查询中使用的 T2 仅在这些括号内可见。

SELECT 
    T1.MFRFMR03 AS Seq, 
    T1.MFRFMR04 AS [Desc], 
    T1.MFRFMR0S AS M2M, 
    T1.MFRFMR0Q AS Std_Labor, 
    T1.MFRFMR0M AS Std_Setup, 
    T1.MFRFMR0R AS Std_Units 
FROM 
    T1 LEFT JOIN 
                        (
                            SELECT 
                                T2.MAJRTEPLT, 
                                T2.MAJRTEID 
                            FROM 
                                T2 
                            WHERE 
                                (
                                    ((T2.PLT)=[Enter Plant Number]) 
                                    AND 
                                    ((T2.ITMID)=ucase$([Enter Part Number]))
                                )
                        ) SUBQ ON ((T1.MFRFMR01)=(SUBQ.MAJRTEPLT)) AND ((T1.MFRFMR02)=(SUBQ.MAJRTEID))
...