SELECT
ew.*,
(SELECT e.title FROM `eud_courescatalog` e WHERE e.cid=ew.cid AND e.tid=ew.`sid`)AS catalogue_title,
(SELECT ei.realName FROM `edu_course` e LEFT JOIN `edu_institutionitem` ei ON e.instituId=ei.uid WHERE e.tid=ew.cid)AS school_name
FROM `edu_workteacher` ew
LEFT JOIN `edu_workitem` em ON ew.`tid`=em.`pid`
LEFT JOIN `edu_lesson` el ON ew.`cid`=el.`cid`
WHERE em.`pid` NOT IN(
SELECT
ew.`tid`
FROM `edu_workteacher` ew
LEFT JOIN `edu_lesson` el ON ew.`cid`=el.`cid`
LEFT JOIN `edu_workitem` ee ON ew.`tid`=ee.`pid` AND ee.`formID`=el.`uid`
WHERE ee.`formID`=#{query.uid} AND el.`uid`=#{query.uid}
GROUP BY ew.`tid`
)
AND el.`uid`=#{query.uid}
GROUP BY ew.`tid`
SELECT ew.*, (SELECT e.title FROM `eud_courescatalog` e WHERE e.cid=ew.cid AND e.tid=ew.`sid`)AS catalogue_title, (SELECT ei.realName FROM `edu_course` e LEFT JOIN `edu_institutionitem` ei ON e.instituId=ei.uid WHERE e.tid=ew.cid)AS school_name .