I'm not a fan of correlated subqueries (The reason:
SQL server performance - Death by correlated subqueries - SQL Service[
^] ) so an alternative approach is to use Common Table Expressions (
WITH common_table_expression (Transact-SQL) - SQL Server | Microsoft Docs[
^]). E.g. something like (NB untested)
;with cte as
(
select Activate,AadhaarNo,gender,emailID,joindate,qua,
CASE WHEN classCourse='class' THEN CM.classname
WHEN classCourse='course' THEN CD.subCrs
ELSE ''
END as course
from tbStudInfo SI
left join tbCrsDetail CD on SI.cID=CD.crID
left join [bitcrj_database].[tbClassMaster] CM on SI.cID=CM.id
)
select Activate,AadhaarNo,gender,emailID,joindate,qua
from cte
Where course=''
Points to note:
- Both of us (Solutions 1 and 2) have used Table Aliases - it saves a lot of typing and makes the sql easier to read
- I've added an ELSE to your case - because you are using left join it is possible that there are no matches on the joined tables and classCourse would be returned as NULL not ''
- the semi-colon in
;with
is not necessary if you terminate the previous statement with ;