Using if-then or case-when-then to select from dataset-CodePudding

I have a dataset with students in 1st, 2nd and 3rd grades. I want to select the names of all students in 1st and 2nd grades, but only students named John, Jane or Smith from 3rd grade. Here is what I have so far:

select 
first_name, 
grade_level

from table_with_all_students_info

-- I need an if or case when statement here saying if student is in third grade, their first name selected can only be John, Jane or Smith. My attempt is below. 

case when grade_level in ('3rd grade')
then first_name in ('John', 'Jane', 'Smith')

I'm not sure what I'm getting wrong there but I'd appreciate some help. Thanks

CodePudding user response：

You can UNION two queries such as this:

    select first_name, grade_level
    from table
    where grade_level in ('1st','2nd')
    union
    select first_name, grade_level
    from table
    where grade_level = '3rd'
    and first_name in ('John','Jane','Smith')

CodePudding user response：

Use where with or:

select 
  first_name, 
  grade_level
from table_with_all_students_info
where (grade_level = '3rd grade' and first_name in ('John', 'Jane', 'Smith'))
  or grade_level in ('1st grade', '2nd grade')

If there are only 3 grades in your data, you can replace grade_level in ('1st grade', '2nd grade') with grade_level != '3rd grade'

CodePudding user response：

You almost got it, just need to put the case when in the where clause:

select
    grade_level,
    first_name
from table_with_all_students_info
where 
    case when grade_level = '3rd grade' then first_name in ('John', 'Jane', 'Smith') end;