Case trick; "Empty set" handling
A small hack that allows you to process the "Empty set" in older versions of MySQL (4+), in which there are no functions.
When writing a small request, I stalled on the following problem: there was no requested information in the database. It looked something like this:
In this case block, as I just did not try to get one: functions, mathematical, logical operations, but it helped nothing: in response, when testing, there was only “Empty set (0.06 sec)”.
I already decided to leave the crutch written almost immediately in the form:
My old friend helped me find a solution and advised the following approach: use column numbers instead of their names, as well as use the usual count. As a result, the correct query looks like this:
see also:
dev.mysql.com/doc/refman/4.1/en/all-subqueries.html
When writing a small request, I stalled on the following problem: there was no requested information in the database. It looked something like this:
set @var = ( select case when t1.field is not null then t1.field else 1 end as field_name from table1 t1 join table2 t2 on t1.tid = t2.id where t1.field2 = '_UNEXISTING_IN_DB_VALUE_' order by t1.tid desc limit 1 );
In this case block, as I just did not try to get one: functions, mathematical, logical operations, but it helped nothing: in response, when testing, there was only “Empty set (0.06 sec)”.
I already decided to leave the crutch written almost immediately in the form:
set @ var2 = (select if (@var is null, 1, @ var));
My old friend helped me find a solution and advised the following approach: use column numbers instead of their names, as well as use the usual count. As a result, the correct query looks like this:
set @var = ( select case when count (1)> 0 then t1.field else 1 end as field_name from table1 t1 join table2 t2 on t1.tid = t2.id where t1.field2 = '_UNEXISTING_IN_DB_VALUE_' order by t1.tid desc limit 1 );
see also:
dev.mysql.com/doc/refman/4.1/en/all-subqueries.html