Query based on an input array and get the same result in the same order

Question

I want to get active status of an array of scene_id for a specific uuid; for example the input would be scene_id in(1,2,3) and uuid=2, so it should return (1,0,1) in the same order of passed scene_id

scene_id  uuid active
1           2   1
3           2   1
5           2   1
7           2   1
1           3   1
2           3   1
5           3   1
5           4   1
1           4   1
1           8   0

I tried :

select active from likes where scene_id in (1,2,3) and uuid=2

but it returns (1,1); it ignores 0, because there is no scene_id=2 and uuid=2 If you need more clarification, please let me know!

Answer 1

If you can insert your specific uuid records into a new table then it is easier:

insert into newTable
    select scene_id, uid
        from likes
        where scene_id in(1,2,3) and uuid=2

Then your query should be like this:

select ifNull(active, 0) as r 
from
    likes
        left join newTable on likes.scene_id=newTable.sceneId and likes.uuid=newTable.uuid

You can also specity particular ordering to this last query as well.