How to create a temporary table using ifnull

Question

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
    task_is_key_temp
    task_id_temp
    proj_name_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:

ifnull(Task_code,
      CREATE TABLE Key_temp ...)

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

Edit

Here's the table structure:

Pcrs
Num_doc|
1      |
2      | 
-      |



Projetos_main
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.

The logic is a bit complicated, but if it is absolutely necessary i can include it on the question. Should i? — RazorFinger, Jul 28 '16 at 17:45
I basically need to include information from a different row and column on the same table — RazorFinger, Jul 28 '16 at 17:46
As it stands, your question lacks the necessary details to answer properly, you should include **all** that is relevant, including the logic you are trying to implement — Matteo Tassinari, Jul 28 '16 at 17:47
Could you please include the actual table structure, or even better, create an SQL fiddle? — Matteo Tassinari, Jul 28 '16 at 18:00
Edited again. Can't really do a Fiddle, since my problem is mostly one of syntax. — RazorFinger, Jul 28 '16 at 18:12

score 1 · Answer 1 · answered Jul 28 '16 at 17:46

1

IFNULL returns one of the two arguments depending on the variability of null value of the column.

Create table Temp select  ...

Above is not a valid return value. This query won't work.

answered Jul 28 '16 at 17:46

FallAndLearn

4,035
1
18
24

score 1 · Accepted Answer · answered Jul 28 '16 at 18:17

1

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

SELECT pm.*
     , IFNULL(
         pm.Task_code,
         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

answered Jul 28 '16 at 18:17

Matteo Tassinari

18,121
8
60
81

This is exactly what i needed, thank you very much for your patience. – RazorFinger Jul 28 '16 at 18:21
1

You're welcome :) please beware that this might run slowly if there are many rows because of the subquery. – Matteo Tassinari Jul 28 '16 at 18:23

How to create a temporary table using ifnull

2 Answers2