-
problem in select query
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.
-
eh?¿ wtf isnt that too *easy*
-
Originally posted by pando
eh?¿ wtf isnt that too *easy*
Yes.
Code:
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.
-
Originally posted by gandolf989
Code:
SELECT Student_id
FROM mytable
WHERE course = 'java' AND
course = 'c';
This query will of course *never ever* return any row! Too easy, eh?
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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>
-
How about ...
Code:
select student_id
from my_table
where course in ('java','c')
group by student_id
having count(distinct course) = 2
/
-
Originally posted by jmodic
This query will of course *never ever* return any row! Too easy, eh?
Touche!
Jeff Hunter
-
Originally posted by marist89
Touche!
Touche
-
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.
PHP Code:
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
Last edited by tamilselvan; 08-25-2004 at 01:45 PM.
-
this should work fine:
PHP Code:
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;
- Cookies
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|