Imi Imi - 1 month ago 9
MySQL Question

Can't open temporary table MySql

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
WHERE C.CinemaAllowSales='Y'
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' );

Answer

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)
)