What is the SQL query in SQLite for filling a table of foreign keys referring to other primary keys respectively

Question

In a SQLite database I have a Tasks table

task_id	task_name
1	2D
2	3D

a Codes table

code_id	code_name	for_2D	for_3D
1	code1	0	1
2	code2	1	0
3	code3	1	1

and a table to fill code_task

code_id	task_id

In the last two columns of Code table, 1 represents true and 0 represents false. I want to write the sql to fill the code_task table with code_id where 2D or 3D column has value 1 and task_id of "2D" and "3D". So the idea result should be

code_id	task_id
1	2
2	1
3	1
3	2

I make such design so that I only need to update code_task table if the relationship between task and code gets modified.

Could someone help me with the sql query?

Answer 1

This can be done using INNER JOIN with two conditions applied to the columns for_2D and for_3D :

SELECT code_id, task_id
FROM Codes c
INNER JOIN Tasks t ON ( c.for_2D = 1 AND t.task_name = '2D' )
                      OR ( c.for_3D = 1 AND t.task_name = '3D');

Results :

code_id task_id
1       2
2       1
3       1
3       2

Demo here