-2

I have 2 tables enroll(sid, grade, dname, cno, sectno) and student(sid, sname, sex, age, year, gpa)

I have to write query asking for the ids of students that took only sections that had more than 10 students in them.

Why this query is not giving me correct result?

select student.sid
from student
where student.sid IN (select student.sid
                      from enroll
                      group by sectno, cno, dname
                      having count (distinct enroll.sid) > 10)

What about this query, is it correct?

select distinct sid
from enroll e1
where 10 < (select count (*)
            from enroll e2
            where e2.sectno = e1.sectno
              and e2.cno = e1.cno
              and e2.dname = e1.dname)
order by sid
wildplasser
  • 43,142
  • 8
  • 66
  • 109
Ahmed
  • 1
  • 1
  • Show the expected result and the output you are getting now. The query seems almost ok. By the way, why are you doing the self-joins? – AT-2017 Nov 20 '16 at 09:41

2 Answers2

1

Have a sub-query that returns sectno's for sections with less than 10 students. Do NOT IN that result.

select distinct sid
from enroll
where sectno not in (select sectno
                     from enroll
                     group by sectno
                     having count(sid) < 10)
jarlh
  • 42,561
  • 8
  • 45
  • 63
0

Try using something like the below:

select sid
from enroll e1
where (select count (sectionId) from enroll e2) > 10
order by sid ASC

Note: Don't use distinct in this scenario.

AT-2017
  • 3,114
  • 3
  • 23
  • 39
  • It is not correct, this is giving me all the sid in the database. – Ahmed Nov 20 '16 at 10:05
  • Yes and it will return only those sections that has more than 10 students. I guess, in the table, all the sections have more than 10 students. Check the table. – AT-2017 Nov 20 '16 at 10:16
  • We have many sections which have sid count < 10 (i.e enrollment less then 10) – Ahmed Nov 20 '16 at 10:29
  • Works perfect on my side. Try to share the table structure and sample data in it. – AT-2017 Nov 20 '16 at 10:34
  • Your sub query is not correlated. It will return the same value no matter what row is being processed in the outer query. So, all rows pass or all rows fail – MatBailie Nov 20 '16 at 13:58
  • Also, one student can enroll in multiple sections. So the outer query needs a GROUP BY or DISTINCT – MatBailie Nov 20 '16 at 14:05