Reputation: 1606
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
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
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