NOT IN, Subquery Opmisation Help PLZZZ

Hi we are migrating postgres to Mysql, and there is some serious issues with Subqueries. Can you please help me out in this query ??

SELECT

COUNT(X.jmodule) as total,

SUM(CASE WHEN X.result = ‘PASS’ THEN 1 ELSE 0 END) as pass,

SUM(CASE WHEN X.result <> ‘PASS’ THEN 1 ELSE 0 END) as fail,

SUM(CASE WHEN X.result <> ‘PASS’ AND X.jmodule
NOT IN (
SELECT Y.jmodule
FROM
(SELECT jmodule,result,opid,fid,date FROM e6lmc UNION ALL SELECT jmodule,result,opid,fid,date FROM e6op125 UNION ALL SELECT jmodule,result,opid,fid,date FROM e6op150) Y WHERE Y.jmodule = X.jmodule AND Y.date > X.date ) THEN 1 ELSE 0 END ) as hard,
count(distinct (CASE WHEN X.result = ‘PASS’ THEN X.jmodule END)) as outp,
SUBSTRING(RTRIM(jmodule),6,7) AS modas,
result,
opid,
fid
FROM (
SELECT jmodule,result,opid,fid,date,mrn FROM e6lmc
UNION ALL
SELECT jmodule,result,opid,fid,date,mrn FROM e6op125
UNION ALL
SELECT jmodule,result,opid,fid,date,mrn FROM e6op150) X
WHERE
X.date > ‘2014/08/28 05:00:00’ AND X.date < ‘2014/08/28 13:00:00’ AND X.jmodule IS NOT NULL
group by
modas,result,opid,fid
order by
total desc

Thanks