Add column based on multiple column conditions SQL

I have two mock tables like this:

Table1:

ID	Type
1	EE
2	MM
3	EE

Table2:

ID	Col1	Col2
1	Red	Yes
2	Blue	Yes
3	Red	No

I would like an SQL query that adds a new column to Table 2 which is identified according to the following logic: Electrical: Type: EE AND Col1: Red AND Col2: Yes Mechanic: Type: MM AND Col1: Blue AND Col2: Yes

I expecting this result:

Table2:

ID	Col1	Col2	NewColumn
1	Red	Yes	Electric
2	Blue	Yes	Mechanic
3	Red	No

Solution

You can implement that logic almost exactly as you wrote it, in a case expression like so:

case 
    when Type = 'EE' and Col1 = 'Red' AND Col2 = 'Yes' then 'Electric'
    when Type = 'MM' AND Col1 = 'Blue' AND Col2 = 'Yes' then 'Mechanic'
end

as a select query, note here you should include table aliases for each column reference:

select
    t1.id, t1.type, t2.col1, t2.col2,
    case 
        when t1.Type = 'EE' and t2.Col1 = 'Red' AND t2.Col2 = 'Yes' then 'Electric'
        when t1.Type = 'MM' AND t2.Col1 = 'Blue' AND t2.Col2 = 'Yes' then 'Mechanic'
    end as NewColumn
from table1 as t1
inner join table2 as t2 on t1.id = t2.id