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

user_14117742 0
Viewed 2213

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.

sql
2 Answers

You could try:

SELECT t1.Student, CASE WHEN t2.Student IS NOT NULL THEN 'Yes' ELSE 'No' END AS HasMath
FROM Table1 t1
LEFT JOIN Table2 t2
    ON t2.Student = t1.Student AND t2.Subject = 'math';
user_18632290

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.

user_11440350
Related