The official document of mySql says temp table can not be used twice in a query. I could not figure it out from other solutions. I have a query where I have to refer a temp table twice. How can I avoid or change it achieving the same result.
SELECT DISTINCT Cinema_Name
FROM tblCinema C
AND C.Cinema_ID IN ( SELECT C_ID FROM tmp WHERE Flag='Y' )
AND C.Cinema_ID NOT IN ( SELECT C_ID FROM tmp WHERE Flag='E' );
You can use a
left join to link the temp table and then filter the data with some bool logic
SELECT C.Cinema_Name FROM tblCinema C LEFT JOIN tmp on C.Cinema_ID = tmp.C_ID WHERE C.CinemaAllowSales='Y' AND ( (tmp.Flag = 'Y' AND tmp.C_ID IS NOT NULL) OR (tmp.Flag = 'E' AND tmp.C_ID IS NULL) )