Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2011
    Posts
    3

    Unanswered: =ALL or =ANY Problems

    I need to run a query for students that took ALL the following courses: 6710, 6711, 1032, 1035 and ANY of the following courses: 3061,3065

    Here is my query:
    Code:
    select distinct student_number, last_name, first_name, gender, ethnicity, students.grade_level, course_name, course_number
    from students inner join storedgrades on students.id=storedgrades.studentid
    where (students.grade_level >'9' and students.grade_level <= '12')
    and (course_number = all('6710', '6711', '1032', '1035') and course_number=Any('3061','3065'))
    order by last_name, first_name, grade_level, course_number
    The problem is that I don't get any results. But when I change the query to OR instead of AND I can see that students took the courses required (6710, 6711, 1032, 1035 and any 3061, 3065). What am I doing wrong?

    Thanks in advance.
    Last edited by andretru2; 02-28-11 at 16:43.

  2. #2
    Join Date
    Feb 2011
    Posts
    3
    Sorry. Double Post.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >What am I doing wrong?
    Please realize that we don't have your tables & we don't have your data.
    Therefore we can't run, test or improve your posted SQL.
    It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
    It would be helpful if you provided DML (INSERT INTO ...) for test data.
    It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.

    Code:
    SELECT DISTINCT student_number,
                    last_name,
                    first_name,
                    gender,
                    ethnicity,
                    students.grade_level,
                    course_name,
                    course_number
    FROM   students
           inner join storedgrades
             ON students.id = storedgrades.studentid
    WHERE  ( students.grade_level > '9'
             AND students.grade_level <= '12' )
           AND ( course_number = ALL ( '6710', '6711', '1032', '1035' )
                 AND course_number = ANY ( '3061', '3065' ) )
    ORDER  BY last_name,
              first_name,
              grade_level,
              course_number
    Last edited by anacedent; 02-28-11 at 17:19.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Maybe this is more logical:
    Code:
    -- Etc --
           AND ( course_number = ALL ( '6710', '6711', '1032', '1035' )
                 OR course_number = ANY ( '3061', '3065' ) )
    -- Etc --
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Feb 2011
    Posts
    3
    Not sure that's correct.
    That query will ignore ALL the courses ('6710', '6711', '1032', '1035') and will only list ('3061', '3065') courses.
    I need students that took ALL '6710', '6711', '1032', '1035' and at least one of 3061, 3065.
    I know for a fact that there are students that took all the courses required.
    @anacent, I can't provide DDL or DML at the moment. I'm only accessing the database. Can you help with the logic? am I missing something obvious?
    Thank you.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
      1  SELECT last_name, salary FROM employees
      2    WHERE salary =
      3*   ALL ( 1400, 3000)
    SQL> /
    
    no rows selected
    
    SQL> select count(*) from employees where salary = 3000;
    
      COUNT(*)
    ----------
    	 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •