Re: Sql Query Help please

From: Jomarlen <jomarlen_at_aol.com>
Date: 1997/10/30
Message-ID: <19971030234700.SAA05623@ladder02.news.aol.com>#1/1

Hi John

A query that will satisfy your criteria is:

SELECT *
  FROM students a
WHERE EXISTS
(SELECT 1

     FROM classes
    WHERE class_name = 'CLASS_1'
      AND stud_nbr = a.stud_nbr)
  AND EXISTS
(SELECT 1

     FROM classes
    WHERE class_name = 'CLASS_2'
      AND stud_nbr = a.stud_nbr)
  AND EXISTS
(SELECT 1

     FROM classes
    WHERE class_name = 'CLASS_3'
      AND stud_nbr = a.stud_nbr)

Another possible solution, which would be more flexible, would be to create a stored function.

Create or Replace
FUNCTION Classes_Attended(p_stud_nbr NUMBER

                         ,p_Classes VARCHAR2)

RETURN VARCHAR2
IS
Return_Value VARCHAR2(5);

 t_comma NUMBER;
 t_Classes VARCHAR2(255) := UPPER(p_Classes)||','; -- This assumes class_name
 t_Class   classes.class_name%TYPE;                          -- is uppercase.
 Comma appended
                                                   -- to simplify parsing.

CURSOR Check_Class_Cur IS
  SELECT 'TRUE'
    FROM classes
   WHERE stud_nbr = p_stud_nbr
     AND class_name = t_Class;

BEGIN
  WHILE t_Classes IS NOT NULL LOOP
   t_Comma := instr(t_Classes,',');
   t_Class := substr(t_Classes,1,t_comma - 1);

Return_Value := 'FALSE';

   OPEN Check_Class_Cur;
    FETCH Check_Class_Cur
     INTO Return_Value;
    CLOSE Check_Class_Cur;

EXIT WHEN Return_Value = 'FALSE'; -- 1 FALSE is enough

t_Classes := substr(t_Classes,t_comma + 1); END LOOP;

  RETURN Return_Value;                          -- Note we return char NOT
 boolean
 END;                                           -- SQL*Plus does not allow

boolean.

Note that the class names are in a single, comma separated, string (i.e. The WHOLE string is in quotes, not the individual classes). This allows for any number of classes up to a string length of 255 (which could be increased).

The syntax for the query now becomes

SELECT *
FROM students
WHERE classes_attended(stud_nbr,'class_1,class_2,class_3') = 'TRUE';

Conversely you could select the students who have NOT attended one or more of the required classes

SELECT *
FROM students
WHERE classes_attended(stud_nbr,'class_1,class_2,class_3') = 'FALSE';

This is a quick adaptation of something similar in one of my applications. I have only given it a cursory test, but it should be OK.

Regards
John

John C. Lennon
Utility Partners Inc.
4300 West Tropicana Blvd LVO-UPL
Las Vegas NV 89103

FOR UTILITY PARTNERS EMPLOYMENT
OPPORTUNITIES VISIT www.utilpart.com

e-mail: jomarlen_at_aol.com
Web Site: http://members.aol.com/jomarlen/

The views expressed in this message
are those of the author and not
necessarily those of U.P. Inc.
and/or its employees.

>I have 2 tables, students and classes_attended in a 1 to many
>relationship.
>
>I need a query that will select students that have attended a specific
>set of classes and only if they have attended ALL the classes
>specified ( that's why a OR where clause dosen't work) - any ideas?
>
>stud_nbr is the key field in both tables.
>
>Thanks,
>John Greco
>Oracle DBA suffering from 'out-of-memory' error in brain.

Received on Thu Oct 30 1997 - 00:00:00 CST