There are 3 tables as below,
table a ( eid, COLUMNA, COLUMNB, COLUMNC, COLUMND)
1001,1,1,0,1
1002,1,0,0,0
table b (id, description)
1, ABC
2, BCD
3,CDE
4,DEF
Note: description will be related to table1 columns, usually; these are checkboxes on the ui, so desciption = checkbox names from ui.
table c(eid, groupid)
My question is, I need to migrate the data from table1 into table 3 as below
1001,1
1001,2
1001,4
1002,1
Below is my query, but not getting the results as I expected.
SELECT DISTINCT eid, id
FROM (SELECT eid,
CASE
WHEN cola = 1
THEN
(SELECT id
FROM tableb
WHERE description = 'ABC')
WHEN cola = 0
THEN
-1
END
AS coln_a,
CASE
WHEN colb = 1
THEN
(SELECT id
FROM tableb
WHERE description = 'BCD')
WHEN colb = 0
THEN
-1
END
AS coln_b,
CASE
WHEN colc = 1
THEN
(SELECT id
FROM tableb
WHERE description = 'CDE')
WHEN colc = 0
THEN
-1
END
AS coln_c,
CASE
WHEN cold = 1
THEN
(SELECT id
FROM tableb
WHERE description = 'DEF')
WHEN cold = 0
THEN
-1
END
AS coln_d
FROM tablea
WHERE cola = 1 OR colb = 1 OR colc = 1 OR cold= 1) temp_t,
tableb
WHERE coln_a = id OR coln_b = id OR coln_c = id OR coln_d = id
ORDER BY eid, id;
what is the mistake?
