Compare two columns contain the same value in SQL Server

Question

This is the data which I am working on

Declare @mytable TABLE(EmpName varchar(100),MATHS INT,SCIENCE INT)

INSERT INTO @mytable 

VALUES('Ram',50,60)

,('Krishna',100,30)

,('Ramesh',90,90)

I wanted to display the cells which satisfy this condition (Maths=90 and Science=90). Do get this I have used the code below:

SELECT EmpName

,CASE WHEN MATHS=90 THEN CASE WHEN SCIENCE =90 THEN 1 ELSE 0 END END

 FROM @mytable

Is there any other way to achieve it? if yes please share the code.

Answer 1

The other way is using SQL inline if condition. Looking at your question I understand that you wanted to display those 2 subject columns which are scored 90 marks. you can achieve that using the flag and get the answer 1 which means true else 0 means not satisfied. 

In this problem, I am preferring to use the select condition.

SELECT  EmpName,

        IIF((MATHS = 90 AND SCIENCE = 90), 1, 0)

FROM @mytable

Improve your knowledge in data science from scratch using Data science online courses