case statement inside where clause sql server

517 Views Asked by At

I want to put a case condition inside where clause,ie if semester column of second table is null,i need to filter the columns where semester is null. If not,I need to filter the data on another condition.

//if semester is null,I need the query to be like this:

select * from tbl_courses tc
    left join tbl_exams te on tc.courseID = te.courseID
    where te.gradeID=10 and 
    te.semester is null

//if semester is not null,query should be

select * from tbl_courses tc
left join tbl_exams te on tc.courseID = te.courseID
where te.gradeID=10 and 
te.semester is null

For this requirement how can I write a single query from sql itself like the following:

select * from tbl_courses tc
left join tbl_exams te on tc.courseID = te.courseID
where te.gradeID=10 and 
(case when te.semester is null 
  then te.semester is null
  else te.semester = tc.semester END)  

Any way this is not right.Please help me

1

There are 1 best solutions below

1
On BEST ANSWER

Assuming your second select statement should be:

select * from tbl_courses tc
left join tbl_exams te on tc.courseID = te.courseID
where te.gradeID=10 
and te.semester = tc.semester

Try this:

select * 
from tbl_courses tc
left join tbl_exams te on tc.courseID = te.courseID
where te.gradeID=10 
and (
     te.semester = tc.semester
     OR te.semester is null
)