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? :D

[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? :D
Touche!

[gandolf989]

Originally posted by marist89
Touche!

Touche :o

[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;

[tamilselvan]

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

[Cookies]

here is my rewrite but I think Tamil's did the same thing:

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

[akerber00]

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'

[tamilselvan]

3rd method:

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')) ;



Tamil