Query Help. CASE within a WHERE IN SELECT statement

Question

I have a query that uses a WHERE IN SELECT statement to check user levels within a table and return entries based on the level from another table.

SELECT * from table2
where user_level IN (select
         table1.user_level
         from table1
         where user_id=1);

My user levels are L1, L2 and L3.

How can I adjust the query so if the users level is L1, only L1 will be returned, and if the users level is L2 then both L1 and L2 will be returned, and if the users level is L3 then L1, L2 and L3 will be returned?

MySQL 8.*

Thanks

Answer 1

Selecting based on some variable (i.e. @level) can be done like this:

SET @level = 'L2';

select * from users where (@level='L1' and user_level = 'L1' )
                        or (@level='L2' and user_level in ('L1','L2'))
                        or (@level='L3' and user_level in ('L1','L2','L3'))

see: DBFIDDLE

NOTE: I do know this is not a direct answer to your question, and that you need to apply this to your situation.