Board index » cppbuilder » newbie seeking help with sql statement

newbie seeking help with sql statement


2004-10-23 11:08:46 PM
cppbuilder88
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
 
 

Re:newbie seeking help with sql statement

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" < XXXX@XXXXX.COM >wrote:
Quote
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



 

Re:newbie seeking help with sql statement

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" < XXXX@XXXXX.COM >wrote in message
Quote

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.
Quote

HTH,
Nick

"Alan Shiers" < XXXX@XXXXX.COM >wrote:
>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
>
>
>

 

{smallsort}

Re:newbie seeking help with sql statement

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" < XXXX@XXXXX.COM >escreveu na mensagem
Quote
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" < XXXX@XXXXX.COM >wrote in message
news:417a77d9$ XXXX@XXXXX.COM ...
>
>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" < XXXX@XXXXX.COM >wrote:
>>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
>>
>>
>>
>


 

Re:newbie seeking help with sql statement

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" < XXXX@XXXXX.COM >wrote in message
Quote
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" < XXXX@XXXXX.COM >escreveu na mensagem
news:417d401f$ XXXX@XXXXX.COM ...
>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" < XXXX@XXXXX.COM >wrote in message
>news:417a77d9$ XXXX@XXXXX.COM ...
>>
>>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" < XXXX@XXXXX.COM >wrote:
>>>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
>>>
>>>
>>>
>>
>
>


 

Re:newbie seeking help with sql statement

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" < XXXX@XXXXX.COM >escreveu na mensagem
Quote
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

 

Re:newbie seeking help with sql statement

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" < XXXX@XXXXX.COM >wrote in message
Quote
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" < XXXX@XXXXX.COM >escreveu na mensagem
news: XXXX@XXXXX.COM ...
>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
>