Need help with a SQL Statement.
I have an internal table - below is an example
CREATE TABLE #MyTable
The issue I have is setting the value of Courses. If there are multiple courses then I want the value 'Multiple' to appear there otherwise the name of the course. (My internal table is more complicated than above just trying to make it easy.
So my code goes as follows
Insert Into #MyTable
Select ID, Name From Student
Below would work if there was only one course for the student but if there are multiple I want to update Coursename with 'Multiple'.
SELECT ID, CourseID, CourseName
FROM Courses c
INNER JOIN xferNameCourse xnc ON c.CourseID = xnc.CourseID
WHERE nc.ID IN (
SELECT ID FROM #MyTable
SET mt.CourseName = s.CourseName
FROM #MyTable mt
INNER JOIN #Courses c ON mt.ID = c.ID