CODEWITHSUNDEEP
mysql
lil-wolf
lil-wolf

Reputation: 382

How to use set @ variable in mysql?

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.

Upvotes: 2

Views: 85

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28864

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

Upvotes: 1

Related Questions