Nope.
See the result:
SQL> L
1 select student_id from (
2 select distinct student_id,
3 count(*) over (partition by student_id) courses
4 from my_table
5 where course in ('java','c'))
6* where courses = 2
SQL> /
STUDENT_ID
----------
13
15
Tamil
Printable View
Nope.
See the result:
SQL> L
1 select student_id from (
2 select distinct student_id,
3 count(*) over (partition by student_id) courses
4 from my_table
5 where course in ('java','c'))
6* where courses = 2
SQL> /
STUDENT_ID
----------
13
15
Tamil
here is my rewrite but I think Tamil's did the same thing:
PHP Code:
select student_id from (
select student_id,
max(decode(course, 'java', student_id)) java,
max(decode(course, 'c', student_id)) c,
max(case when course not in ('java', 'c') then 'x' end) x
from my_table
group by student_id)
where java is not null and c is not null and x is null
select
a.student_id
from my_table a,
my_table b
where upper(a.course)='java' and
a.student_id=b.student_id and
upper(b.course)='c'
3rd method:
TamilPHP Code:
SQL>
1 select student_id from my_table a
2 where a.course = 'c'
3 and exists ( select null
4 from my_table b
5 where b.student_id = a.student_id
6 and b.course = 'java')
7 and not exists
8 ( select null
9 from my_table c
10 where c.student_id = a.student_id
11* and c.course not in ( 'c', 'java')) ;