469,942 Members | 2,496 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,942 developers. It's quick & easy.

What is the Query?

3
I have 3 tables student(s_id,s_name),course(c_id,c_name) and student_course(s_id(FK),c_id(FK)).
I would Like to select those students Name who have enrolled more then and equal to 3 courses.
What is the Query?
Dec 5 '09 #1
3 1450
debasisdas
8,127 Expert 4TB
That seems to be a home work / assignment.

Kindly post the code that you have tried / working on.
Dec 5 '09 #2
nbiswas
149 100+
Try this

First let's create some sample data

Expand|Select|Wrap|Line Numbers
  1. declare @student table(sid int, sname varchar(50))
  2. declare @course table(cid int, cname varchar(50))
  3. declare @student_course table(sid int, cid int)
  4.  
  5. insert into @student 
  6.     select 1,'student1' union all select 2,'student2' union all
  7.     select 3,'student3' union all select 4,'student4' union all
  8.     select 5,'student5' union all select 6,'student6'
  9. insert into @course 
  10.     select 101,'course1' union all select 202, 'course2' union all
  11.     select 303,'course3' union all select 404, 'course4' union all
  12.     select 505,'course5' union all select 606, 'course6' 
  13. insert into @student_course
  14.     select 1,101 union all select 1,202 union all select 1,303 union all
  15.     select 1,404 union all select 1,505 union all select 1,606 union all
  16.     select 2,101 union all select 2,505 union all select 2,606 union all
  17.     select 3,303 union all select 3,202 union all select 4,303 union all
  18.     select 4,404 union all select 4,202 union all select 5,101 union all 
  19.     select 6,101 union all select 6,202
Query 1:

Expand|Select|Wrap|Line Numbers
  1. select s.sid,s.sname from @student s
  2. inner join
  3. (    
  4. select sid,COUNT(cid) Courses from @student_course 
  5. group by sid
  6. having (COUNT(cid)>=3))X
  7. on X.sid = s.sid 
Query 2:
Expand|Select|Wrap|Line Numbers
  1. select sid,sname from @student where sid in(
  2. select sid from @student_course 
  3. group by sid
  4. having (COUNT(cid)>=3))
The output(in both the cases)

Expand|Select|Wrap|Line Numbers
  1. sid    sname
  2. 1    student1
  3. 2    student2
  4. 4    student4
Hope this helps
Dec 7 '09 #3
mzahid
3
Thank you very much sir.....
Dec 7 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by J Sharman | last post: by
3 posts views Thread by White Bilky | last post: by
1 post views Thread by Dot Net Daddy | last post: by
2 posts views Thread by Aaron Reimann | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.