SQL - If Else inside the return values?

Question

SELECT

subj.SubjectID            AS [ID],
subj.SubjectDescription   AS [Subject],
enrol.StuSubjEnrolmentID  AS [IsEnrol]


 FROM [PATHWAYS].[Subjects] AS [subj]

 LEFT JOIN 
 [STUDENT].[StuSubjEnrolment] AS [enrol]
 ON subj.SubjectID = enrol.SubjectID

 LEFT JOIN
 [STUDENT].[Student] AS [stu]
 ON enrol.StuID = stu.StuID
 AND stu.StuID = @stuID

Hi guys, can anyone please guide me on this? Thank you in advance. I'm really clueless on this.

How can I write this statement in a way that....

When IsEnrol is not null, it returns 0. Else returns 1? I want that particular column to show only 1 or 0.

you would like each IsEnrol row to either be 1 or 0 if it's NULL / Not NULL? How do stored procedures come into this? — John Smith, Apr 03 '16 at 17:44
Possible duplicate of [How to perform an IF...THEN in an SQL SELECT?](http://stackoverflow.com/questions/63447/how-to-perform-an-if-then-in-an-sql-select) — Kyle Hale, Apr 03 '16 at 17:44
o ok..thx guys! yeap the answer is there. Thank you very much :D — DriLLFreAK100, Apr 03 '16 at 17:50

score 1 · Accepted Answer · answered Apr 03 '16 at 17:46

1

try this

SELECT

 subj.SubjectID            AS [ID],
 subj.SubjectDescription   AS [Subject],
 CASE WHEN enrol.StuSubjEnrolmentID is not null then 0 else 1 end as AS [IsEnrol]

 FROM [PATHWAYS].[Subjects] AS [subj]
 LEFT JOIN [STUDENT].[StuSubjEnrolment] AS [enrol]
 ON subj.SubjectID = enrol.SubjectID
 LEFT JOIN  [STUDENT].[Student] AS [stu]
 ON enrol.StuID = stu.StuID
 AND stu.StuID = @stuID

answered Apr 03 '16 at 17:46

Jande

1,695
2
20
32

Thank you very much. That's the answer to it :D – DriLLFreAK100 Apr 03 '16 at 17:50

score 1 · Answer 2 · answered Apr 03 '16 at 17:52

Assuming you are using SQL Server 2012 or later, you could use IIF function-

SELECT

subj.SubjectID            AS [ID],
subj.SubjectDescription   AS [Subject],
iif(enrol.StuSubjEnrolmentID is null,1,0)  AS [IsEnrol]


 FROM [PATHWAYS].[Subjects] AS [subj]

 LEFT JOIN 
 [STUDENT].[StuSubjEnrolment] AS [enrol]
 ON subj.SubjectID = enrol.SubjectID

 LEFT JOIN
 [STUDENT].[Student] AS [stu]
 ON enrol.StuID = stu.StuID
 AND stu.StuID = @stuID

If you are using a version of SQL Server earlier than 2012, then the Case statement method from @dejan87's post would be the best solution.

SQL - If Else inside the return values?

2 Answers2