Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    4

    Unanswered: Help joining two queries

    Hi all.

    I'm trying to do is implement a check that prevents particular student from registering for the same (read: similar) class twice.

    I've got two tables (well actually three, but the third one is irrelevant)
    Example: I've got a student 'zzz' who has registered in a MATH 101 class
    with PK (crn_id) 99999. Now he wants to register for class crn_id 10184,
    which happens to be just another MATH 101 class.

    I want to prevent him from registering by making sure that a similar class doesn't exist in class_reg table.

    This is how I'm trying to do this:

    1. Retrieving the class_name for the class to be added (lets say 10184).
    SELECT classes.class_name FROM classes WHERE classes.crn_id = 10184;

    Lets say that above returns 'MATH 101'


    Now check if any of student's currently registered classed match the above
    result, if they do then do not allow duplicate registration.

    2. Select some field like class_name for every record
    SELECT classes.class_name
    FROM classes, class_reg
    WHERE class_reg.student_id='zzz' AND classes.crn_id = ' (Result from #1) ';

    #2 doesn't work as expected.

    Perhaps joining the two queries might solve the problem, unfortunatelly
    after two days I'm unable to get it to work properly.

    I'm running MySQL v3.23.58.

    And this is the structures for classes and class_reg tables:

    CREATE TABLE classes (
    crn_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    semester_id INT UNSIGNED NOT NULL,
    class_name CHAR(8) NOT NULL,
    class_section CHAR(3) NOT NULL,
    class_desc VARCHAR(255) NOT NULL DEFAULT "Not Available",
    prof_name VARCHAR(50) NULL,
    location CHAR(6) NOT NULL,
    credit_hours TINYINT UNSIGNED NOT NULL,
    max_students TINYINT UNSIGNED NOT NULL DEFAULT 10,
    num_students TINYINT UNSIGNED NOT NULL DEFAULT 0,
    last_modified TIMESTAMP NOT NULL
    );

    CREATE TABLE class_reg (
    student_id INT UNSIGNED NOT NULL,
    crn_id INT UNSIGNED NOT NULL
    );

    Any help would be greatly appreciated.

  2. #2
    Join Date
    Mar 2004
    Posts
    8
    Plz elaborate more .

Posting Permissions

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