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.

Plz elaborate more .