I have the following issue:
I have several tables in my Database, in order to check for a specific criteria I have to join several tables, where I use the following statement:
SELECT *
FROM (SELECT * FROM (((((((((SELECT * FROM table1 WHERE tab1_id = 1) AS A
INNER JOIN (SELECT * FROM table2 WHERE tab2_variable IS NOT NULL) AS B
ON A.variable = B.variable)
INNER JOIN (SELECT table3.*, IIF(year='XXXX', 0, 1) AS flag FROM table3) AS C
ON A.h_name = C.h_name)
INNER JOIN (SELECT * FROM table4 WHERE s_flag = 0) AS D
ON C.v_type = D.v_type)
INNER JOIN table5
ON C.ng_type = table5.ng_type)
INNER JOIN table6
ON C.part = table6.part)
INNER JOIN table7
ON C.ifg = table7.ifg)
INNER JOIN table8
ON C.v_type = table8.v_type AND C.ng_typ = table8.ng_typ AND C.ntr_flag = table8.ntr_flag)
INNER JOIN table9
ON table8.ifg_sii_id = table9.ifg_sii_id)) AS F
LEFT JOIN table10
ON F.risk = table10.risk AND C.v_type = table10.v_type
AND F.series = table10.series
This statement fails. But when I remove one the following two join-conditions in the last Left JOIN
, it works as intended:
F.risk = table10.risk
and/or C.v_type = table10.v_type
They are both of type CHAR
, whereas series is type TINYINT
, I guess it has something to do with joining on multiple conditions with strings, but I’m not able to find a workaround, any ideas?
Advertisement
Answer
according to your current SQL, Table C
is not visible as it’s a sub query within F
. instead of C.v_type
you need to use F.c_v_type
and the c_v_type
field must come from C
table like. select v_type as c_v_type from table3... as C