I have data in Table2 which I need to use as the key to retrieve information in Table1. Table2 contains information about the part number including its plant of manufacture and production process. I need to pass to Table2 the warehouse (plant) and the part number and have it pass back to me, the production plant id and the process id. I've attempted with the SQL below but its throws a syntax error that simply says "Syntax error in JOIN operation". I'd rather not have to split into separate sql statements :/
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;
It's because your subquery doesn't have an alias. The T2 you're using within the subquery is only visible from within those parenthesis.
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))
...