I want set a select list as variable and do some conditional check on it:
This is my code which is not working:
set @temp = (select docId from table_1);
SELECT
id,
CASE when id IN (@temp) then 'TRUE' else 'FALSE' end as visited
FROM table_2;
Also tried this:
set @temp = (select docId from table_1);
SELECT
id,
CASE when (FIND_IN_SET(id,@temp)=1) then 'TRUE' else 'FALSE' end as visited
FROM table_2;
In both case, visited column is coming FALSE for each row.
You can use LEFT JOIN with GROUP BY instead. Left joining may result in duplicate rows corresponding to table_2 (as there can be multiple rows for same id value in the table_1); to handle that, we will use Group By. Count() function can be used to determine if id exists in the table_1 or not, and accordingly determine TRUE/FALSE.
Try the following:
SELECT
t2.id,
CASE WHEN t1.docId IS NOT NULL THEN 'TRUE' ELSE 'FALSE' END AS visited
FROM table_2 t2
LEFT JOIN table_1 t1 ON t1.docId = t2.id
id so you will get one row per id. This is what your earlier query is also supposed to achieve. Unless your actual requirement is different.
select docId from table_1