SQL if exists then "Yes' else "No' in a new column

Question

I would like to create a new column in a table. If the primary key exists in the other table or meets certain criteria, then return 'yes' else return 'no'. Here's an example:

Table 1:

Student
a
b
c
d

Table 2:

Student | Subject
a       | english
a       | math
b       | english
b       | science
b       | match
c       | science
c       | english
d       | math

I'd like to see this column added to Table1:

Student | HasMath
a       | Yes
b       | Yes
c       | No
d       | Yes

So if a student's name exists in the filtered table where Subject = 'Math' then the generated column will return 'Yes', else return 'No'.

Could anyone pls show me how to do it by SQL? Thanks very much.

question from:https://stackoverflow.com/questions/65838809/sql-if-exists-then-yes-else-no-in-a-new-column

Answer 1

You would normally do this using exists:

select t1.*,
       (case when exists (select 1
                          from table2 t2
                          where t2.student = t1.student and t2.subject = 'math'
                         )
             then 'yes' else 'no'
        end) as has_math
from table1 t1;

Unlike Tim's answer, this is guaranteed to return only one row per student, even if there are multiple 'math' rows in the second table.