WHERE-IN or FIND_IN_SET

0
votes

An un-normalised db has a table named batches

The table batches has field student_ids with multiple ids in it. For example 1311, 1319, 1400

From: SELECT student_ids FROM batches WHERE 1311 IN(student_ids)

we get student_ids: 1311, 1319, 1400

And: SELECT student_ids FROM batches WHERE 1319 IN(1311, 1319, 1400)

works fine: 1311, 1319, 1400

but

SELECT student_ids FROM batches WHERE 1319 IN(student_ids)

or

SELECT @student_ids := student_ids FROM batches b; SELECT student_ids FROM batches WHERE FIND_IN_SET(1319, @student_ids)

returns Null

What am I missing? I think I have to convert the first result into an array - but how?

I have tried Dash's suggestions (many thanks Dash) but I', still having trouble

I've also tried SELECT id FROM batches WHERE INSTR(','+1319+',', ','+CAST(student_ids AS CHAR) + ',')

as suggested by Matt Ellen on another post - again only works if the first item matches

1
mysqlwhere-in

1 Answers

0
votes

try this:

SELECT @student_ids := GROUP_CONCAT(CAST(TRIM(student_ids) AS UNSIGNED INTEGER)) 
FROM batches b; 

SELECT student_ids 
FROM batches 
WHERE FIND_IN_SET(1311, @student_ids) ;

or

SELECT student_ids FROM batches WHERE CAST(student_ids AS UNSIGNED INTEGER) IN (1319);