problem in select query
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: problem in select query

  1. #1
    Join Date
    Aug 2004
    Location
    India
    Posts
    1

    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.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    eh? wtf isnt that too *easy*

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,026
    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.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  5. #5
    Join Date
    Jul 2000
    Posts
    296
    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>

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    How about ...
    Code:
    select student_id
    from my_table
    where course in ('java','c')
    group by student_id
    having count(distinct course) = 2
    /
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by jmodic
    This query will of course *never ever* return any row! Too easy, eh?
    Touche!
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,026
    Originally posted by marist89
    Touche!
    Touche

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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 =and java_count=and oth_count 0
    /

    Tamil
    Last edited by tamilselvan; 08-25-2004 at 02:45 PM.

  10. #10
    Join Date
    Oct 2002
    Posts
    182
    this should work fine:

    PHP Code:
    select student_id from (
       
    select distinct student_id,
          
    count(*) over (partition by student_idcourses
       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
  •  


Click Here to Expand Forum to Full Width