MySQL and Temporary tables and CASE syntax


For some reason I've always avoided the use of temporary tables in SQL (MySQL or otherwise) but today I came across a difficult problem that temporary tables made very simple.

I needed to use a case function on a subquery value to return a tri-state value. I.e. yes, no or partial.

The cut down query below demonstrates how it works.

  • First we drop the temporary table in case one has been left lying around.
  • Then we select a query into the temporary table which include some complex subqueries.
  • We can then query the temporary table to do further processing on the result, which in this case depending on the values of the class_count and class_count_assigned allows us to set a field in three states.
    • 'yes' if all users for a class are selected
    • 'partial' if some users are selected
    • 'no' if none are selected.


<cfquery name="q" datasource="#variables.myDSN#">
DROP TEMPORARY TABLE IF EXISTS tmp_exams;
/* Create temporary table query which includes student counts */
CREATE TEMPORARY TABLE tmp_exams
SELECT e.id
, e.name
, (SELECT count(1) FROM tbl_user_classes uXc
WHERE uXc.school_class_id = <cfqueryparam value="#arguments.school_class_id#" cfsqltype="cf_sql_integer">) AS class_count
, (SELECT count(1) FROM tbl_user_classes uXc
INNER JOIN tbl_users_exams uXe ON uXe.user_id = uXc.user_id
WHERE uXc.school_class_id = <cfqueryparam value="#arguments.school_class_id#" cfsqltype="cf_sql_integer">
AND exam_id = e.id) AS class_count_assigned
FROM tbl_exams e;
/* Select out the relevant info */
SELECT e.id
, e.name
, e.class_count
, e.class_count_assigned
,(CASE WHEN class_count_assigned = 0 THEN 'no'
WHEN class_count = class_count_assigned THEN 'yes'
ELSE 'partial' END) AS active
FROM tmp_exams e
WHERE 1=1
ORDER BY e.name
</cfquery>

Mostly posted for my future reference but hope it helps out.

Cheers,
Mark

Related Content


This post was posted in , , , by on