Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

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.

like image 278
Yingdong Zhai Avatar asked Aug 31 '25 02:08

Yingdong Zhai


1 Answers

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.

like image 181
Gordon Linoff Avatar answered Sep 02 '25 16:09

Gordon Linoff