CODEWITHSUNDEEP
sql-servert-sql
DriLLFreAK100
DriLLFreAK100

Reputation: 1606

SQL - If Else inside the return values?

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.

Upvotes: 0

Views: 61

Answers (2)

John Smith
John Smith

Reputation: 7407

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.

Upvotes: 1

Jande
Jande

Reputation: 1705

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

Upvotes: 1

Related Questions