I have following type of tables
DECLARE @tbStudent TABLE (StudentId INT, StudentName NVARCHAR(100))
INSERT INTO @tbStudent (StudentId , StudentName)
VALUES (101,'James'),(102,'Smith'),(103,'Jackson'),(104,'Peter');
DECLARE @tbStudentCourseDetails TABLE (StudentId INT, CourseId INT)
INSERT INTO @tbStudentCourseDetails (StudentId , CourseId)
VALUES (101,1),(101,3),(101,6),
(102,4),(102,3),
(103,5),(103,3),
(104,1),(104,3),
(105,1);
DECLARE @tbCourseDone TABLE (CourseId INT)
INSERT INTO @tbCourseDone(CourseId)
VALUES (1),(3);
SELECT * FROM @tbStudent
SELECT * FROM @tbStudentCourseDetails
SELECT * FROM @tbCourseDone
Now i need to get the StudentId, StudentName,CourseId records where Student has done all the courses defined in @tbCourseDone table i.e. 1 and 3
Output should be
StudentId StudentName CourseId
101 James 1
101 James 3
104 Peter 1
104 Peter 3
What I have tried:
I tried In operator but it is fetching the records of the student where any course is matched
I also tried below query and got the result i want:
DECLARE @RecCount int=(SELECT count(*) FROM @tbCourseDone)
;WITH myCTE
AS
(
SELECT EM.StudentId
FROM @tbStudent EM
INNER JOIN @tbStudentCourseDetails teld ON EM.StudentId = teld.StudentId
INNER JOIN @tbCourseDone t ON t.CourseId=teld.CourseId
GROUP BY EM.StudentId HAVING COUNT(Em.StudentId)=@RecCount
)
SELECT EM.StudentId,StudentName,teld.CourseId
FROM @tbStudent EM
INNER JOIN @tbStudentCourseDetails teld ON EM.StudentId = teld.StudentId
INNER JOIN @tbCourseDone t ON t.CourseId=teld.CourseId
INNER JOIN myCTE cte ON cte.StudentId=EM.StudentId
But it is not optimized query and can be shorten i think. Please help me in getting this result.