RazorFinger RazorFinger - 1 year ago 81
MySQL Question

How to create a temporary table using ifnull

I need to create a query that will show the name of the project with the lowest, yet closest id, that is also considered a "key" project, based on whether or not the Task code is empty. This might seem a bit convoluted, so i'll put it in a simplified code to simplify:

ifnull (Task_Code,

create Key_temp

select id_temp

from projetos_main pm left join pcrs
ON pcrs.num_doc = pm.Task_code

where task_id_temp <= task_id and
task_is_sum_temp = 'true'

order by id desc
limit 1

So, when the task code is null, i want to create a temporary table, populate it with information from my main table, and get one result that is both a "Key project" and has an Id that's lower than the original.

The problem is, i don't know if it is possible to create a temporary table using "Ifnull", as when i do the following:

CREATE TABLE Key_temp ...)

I get a syntax error. So it is impossible or is my code just wrong?


Here's the table structure:

1 |
2 |
- |

Id || Task_is_key || Task_id || Proj_name || Task_code |
1 || False || 3 || Name 1 || 1 |
2 || True || 5 || Name 2 || 2 |
3 || False || 5 || Name 3 || - |

Task_code is a foreign key referencing Num_doc. Not all projects have a Task_Code though, so when that happens i need to show the name of the project with the closest id that is also a Key project.

So it should stop at project 3, realize that Task_code is empty, and get Project 2, which has the same task_id AND is a key_task.

Answer Source

Now that I can see your structure, I'd try this:

     , IFNULL(
         SELECT Task_id -- or perhaps just id?
         FROM Projetos_main pm2
         WHERE pm2.id < pm.id
         ORDER BY pm2.id DESC LIMIT 1
FROM Projetos_main pm
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download