In a many to many relationship, get records of table A associated with more than N number records of table B?

Question

In a many to many relationship, get records of table A associated with more than N number records of table B?

Description:I have Table A(assumed as Student) and Table B(assumed as Course) with their junction Table AB (StudentCourse),For example considering the following tables I need to get all the students with their Id and name who have enrolled in more than three(N) courses

|StudentCourse|
|Id |
|SId |
|CId |

Posted by: Samantha Jones | Posted on: Apr 17, 2019

1 answers

Replies

3

You can get your desired result by simply joining the three tables and placing the required columns in the group by and having clauses as well as shown in the following query.


SELECT A.[Id],A.[Name],count(B.[Id]) as TotalEnrolledCourses  FROM [Student] as A
 inner join [StudentCourse] as AB on A.Id=AB.SId
 inner join [Course] as B on AB.CId=B.Id 
 group by A.[Id],A.[Name] having count(B.[Id])>3

Replied by: Tabish Usman | Replied on: Apr 17, 2019

Reply

×

In a many to many relationship, get records of table A associated with more than N number records of table B?

Replies

Preview