BorlandTalk.com

Alan Shiers · Guest

Hi there,
I need help formulating an sql statement that will display all the courses
in the Courses table but exclude those courses that a student is already
enrolled in.

I'm using a MYSQL database where I'm dealing with two tables named Courses
and Enroll. Their fields look like so:

Courses Table:
Course_ID | Subject_Title | Description

Enroll Table:
Enroll_Date | Student_ID | Course_ID

In the Enroll table I could have a student enrolled in many courses. Hence
under Student_ID field a students id will appear multiple times while noting
the corresponding courses this student is enrolled in.

The Courses table simply contains all the courses available.

So, I need an sql statement that will gather all the Course_ID's from the
Enroll table for a given student and display all the courses from the
Courses table excluding those which this student is already enrolled in.

I've tried several combinations of statements but none of them seem to work.
The following only displays those records this student is enrolled in.
That's not what I'm looking for. How do I change this so that all the
courses get displayed from the Courses table but excludes those which a
student is already enrolled in?

SELECT courses.course_id, courses.subject_title FROM courses, enroll WHERE
enroll.student_id=10 AND enroll.course_id=courses.course_id;

Please advise,

Alan

Nick Rambarransingh · Guest

Good Day,

Try this.

Select *
From Courses_Table
Where
Courses_Table.Course_ID NOT IN
(Select Distinct Course_ID
From Enroll_Table
Where Enroll_Table.Student_ID = 'StudentId')

This says, go to the Enroll table, and for a given studen id, produce a distinct list of course_id's. Then, from the outside select, go and find all the records in the Course table where the Course id is NOT in the list from the inner select.

HTH,
Nick

"Alan Shiers" <ashiers (AT) hfx (DOT) eastlink.ca> wrote:

Alan Shiers · Guest

Hi there,

I tried your query, but mysql keeps saying that there is an sql syntax error
everytime I try a subquery. I looked up in the manual that came with MySQL
the topic of subqueries and even though it states that it support them, it
also states that the query is better re-written as a join. Here is what it
says:
*************************************************
1.8.4.1 SubSELECTs

Subqueries are supported in MySQL version 4.1. See section 1.6.1 Features
Available From MySQL 4.1.

Upto version 4.0, only nested queries of the form INSERT ... SELECT ... and
REPLACE ... SELECT ... are supported. You can, however, use the function
IN() in other contexts.

You can often rewrite the query without a subquery:

SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
This can be rewritten as:

SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id;
The queries:

SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2
WHERE table1.id=table2.id);
Can be rewritten as:

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;
Using a LEFT [OUTER] JOIN is generally much faster than an equivalent
subquery because the server can optimise it better, a fact that is not
specific to MySQL Server alone. Prior to SQL-92, outer joins did not exist,
so subqueries were the only way to do certain things in those bygone days.
But that is no longer the case, MySQL Server and many other modern database
systems offer a whole range of outer joins types.

********************************************************

So, followning their advise, I re-wrote your query as follows:

mysql> SELECT COURSE_ID,SUBJECT_TITLE FROM COURSES LEFT JOIN ENROLL ON NOT
COURSES.COURSE_ID=ENROLL.COURSE_ID WHERE ENROLL.STUDENT_ID=10;
Empty set (0.00 sec)

As you can see I have an empty set from this query. That's not good. This
was the closest I could come to getting something positive (that is, no SQL
syntax errors). Why it doesn't give me what I want, I don't know.

Any other suggestions?

Alan

"Nick Rambarransingh" <NRambarransingh (AT) Rogers (DOT) com> wrote

Jayme Jeffman Filho · Guest

Hello Alan,

When you perform an outer join, records that did not matched the criteria
are included in the result set. The values of the outer joined table are
nulls.
So you ought to remove the word not and add an additional condition
that is table2 ( or table1) field(s) is(are) null.

HTH

Jayme.

"Alan Shiers" <ashiers (AT) hfx (DOT) eastlink.ca> escreveu na mensagem
news:417d401f$1 (AT) newsgroups (DOT) borland.com...

Alan Shiers · Guest

Do you mean something like this?

SELECT COURSE_ID,SUBJECT_TITLE FROM COURSES LEFT JOIN ENROLL ON
COURSES.COURSE_ID=ENROLL.COURSE_ID WHERE ENROLL.STUDENT_ID=10 AND
ENROLL.COURSE_ID IS NULL;

because this gave me an empty set. How should this read?

Alan

"Jayme Jeffman Filho" <jaymenosp (AT) jeffman (DOT) eng.br> wrote

Jayme Jeffman Filho · Guest

Hello Alan,

The correct SQL command is the next :

SELECT COURSES.COURSE_ID, COURSES.SUBJECT_TITLE
FROM COURSES
LEFT OUTER JOIN ENROLL
ON (ENROLL.COURSE_ID = COURSES.COURSE_ID )
WHERE ENROLL.COURSE_ID IS NULL;

You cant have a null record and a student_id = 10 at the same time,
because all the fields of the record are null .

Maybe you should change the "left" word by "right" and also the field
you are checking against null, choosing a field from the "courses" table;
Try a pair of changes involving the table you check fields against null
and the "left" and "right" adjective for the join . One of them will satisfy
your need.

HTH

Jayme.
"Alan Shiers" <ashiers (AT) hfx (DOT) eastlink.ca> escreveu na mensagem
news:417e4a27 (AT) newsgroups (DOT) borland.com...

Alan Shiers · Guest

Thanks Jayme,

I had to work with it a little to get what I wanted, but it finally works:

SELECT courses.course_id, courses.subject_title FROM courses LEFT OUTER JOIN
enroll ON (courses.course_id=enroll.course_id) AND enroll.student_id=10
WHERE enroll.course_id is null;

Thanks for pointing me in the right direction,
Alan

"Jayme Jeffman Filho" <jaymenosp (AT) jeffman (DOT) eng.br> wrote