problem in select query

[ajmal]

Hi,
My table contains two fields std_id & course. data is given as under:
std_id course
===============
12 c
15 java
15 vb
13 java
13 c
14 cobol
14 java
15 c
=======================================================
i want to select the students id who has registered for java and C only means output = 13 from the above data.

Kindly help me for writing select statement.

Thanks,

Ajmal.

[pando]

eh?¿ wtf isnt that too *easy*

[gandolf989]

Originally posted by pando
eh?¿ wtf isnt that too *easy*

Yes.

SELECT Student_id 
  FROM mytable
 WHERE course = 'java' AND
       course = 'c';

btw, ajmal, you should start reading a book on SQL. Perhaps you could read SQL for Mere Mortals. There are many other good SQL books.

[jmodic]

Originally posted by gandolf989

SELECT Student_id 
  FROM mytable
 WHERE course = 'java' AND
       course = 'c';

This query will of course *never ever* return any row! Too easy, eh?

[akkerend]

I don't think this is too easy

SQL> create table my_table
2 (student_id number
3 , course varchar2(30)
4 );

Table created.

SQL>
SQL> insert into my_table values(12,'c');

1 row created.

SQL> insert into my_table values(15,'java');

1 row created.

SQL> insert into my_table values(15,'vb');

1 row created.

SQL> insert into my_table values(13,'c');

1 row created.

SQL> insert into my_table values(13,'java');

1 row created.

SQL> insert into my_table values(14,'cobol');

1 row created.

SQL> insert into my_table values(14,'java');

1 row created.

SQL> insert into my_table values(15,'c');

1 row created.

SQL>
SQL> select * from my_table;

STUDENT_ID COURSE
---------- ------------------------------
12 c
15 java
15 vb
13 c
13 java
14 cobol
14 java
15 c

8 rows selected.

SQL>
SQL> select student_id
2 from my_table
3 where course = 'c'
4 and course = 'java';

no rows selected

SQL>
SQL> select student_id
2 from my_table
3 where course = 'c'
4 or course = 'java';

STUDENT_ID
----------
12
15
13
13
14
15

6 rows selected.

SQL>
SQL> select student_id
2 from my_table
3 having count(decode(course,'c','1','java','1','')) = 2
4 and count(course) = 2
5 group by student_id;

STUDENT_ID
----------
13

SQL>

[slimdave]

How about ...

select student_id
from my_table
where course in ('java','c')
group by student_id
having count(distinct course) = 2
/

[marist89]

Originally posted by jmodic
This query will of course *never ever* return any row! Too easy, eh?

Touche!

[gandolf989]

Originally posted by marist89
Touche!

Touche

[tamilselvan]

Thanks akkerend. You did excellent job.

Slimdave, You missed some thing.

10:39:15 SQL> L
1 select student_id
2 from my_table
3 where course in ('java','c')
4 group by student_id
5* having count(distinct course) = 2
10:39:18 SQL> /

STUDENT_ID
----------
13
15

Once again Big Thanks to akkerend.

Another method:

select student_id
from
(
select student_id ,
       sum(case
        when course = 'c' then 1
         else 0
       end) c_count,
       sum(case
        when course = 'java' then 1
         else 0
       end) java_count,
       sum(case
        when course <> 'java' and course <> 'c' then 1
         else 0
       end) oth_count
from my_table
group by student_id
)
where c_count =1 and java_count=1 and oth_count = 0
/
~ 

Tamil

[Cookies]

this should work fine:

select student_id from (
   select distinct student_id,
      count(*) over (partition by student_id) courses
   from my_table
   where course in ('java','c'))
where courses = 2;