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>
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
There are no comments for this entry.
[Add Comment]