problem in select query - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: problem in select query

  1. #11
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  2. #12
    Join Date
    Oct 2002
    Posts
    182
    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' endx
      from my_table
      group by student_id
    )
    where java is not null and c is not null and x is null 
    - Cookies

  3. #13
    Join Date
    Oct 2001
    Posts
    1

    Try this one.

    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'

  4. #14
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    3rd method:
    PHP 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')) ; 
    Tamil

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