DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 13 of 13

Thread: need help with SQL query

  1. #1
    Join Date
    Feb 2005
    Posts
    31

    need help with SQL query

    Hi there,

    I need help extracting data from three seperate tables. The Tables look like
    so:

    Courses Table:
    COURSE_ID | SUBJECT_TITLE | DESCRIPTION

    Scheduled_Courses Table:
    SCHEDULE_ID | START_DATE | END_DATE | COURSE_ID | INSTRUCTOR_ID

    Enroll Table:
    SCHEDULE_ID | ENROLL_DATE | STUDENT_ID|

    What I want to obtain are the ENROLL.SCHEDULE_ID and COURSES.SUBJECT_TITLE
    fields when passing the student id to the ENROLL table. The fields named
    with an "ID" suffix are just primary and foreign keys. The objective is to
    find out which scheduled courses a particular student is NOT already enrolled
    in.

    I attempted the following (which doesn't work):
    mysql>SELECT S.SCHEDULE_ID, C.SUBJECT_TITLE FROM COURSES AS C, SCHEDULED_COURSES AS S INNER JOIN ENROLL AS E ON (S.SCHEDULE_ID=E.SCHEDULE_ID AND
    S.COURSE_ID=C.COURSE_ID) WHERE E.STUDENT_ID=? AND E.SCHEDULE_ID IS NULL;


    What do I need to change to make this work?

    I want to wind up with the following:
    _________________________________
    SCHEDULE_ID | SUBJECT_TITLE
    _________________________________
    32 | Biology 101
    _________________________________
    46 | English 101


    Thanks,

    Alan

  2. #2
    Join Date
    Feb 2005
    Posts
    31

    SQL query problem

    Hi there,

    I posted earlier, but perhaps the posting was too difficult for some to grasp. So I have tried here to simplify the problem with just using two instead of three tables.

    I have an sql query that needs fixing. Given the two tables ENROLL AND SCHEDULED_COURSES with the following fields:

    ENROLL table:
    ENROLL_DATE | STUDENT_ID | SCHEDULE_ID

    SCHEDULED_COURSES table:
    SCHEDULE_ID | START_DATE | END_DATE | COURSE_ID | INSTRUCTOR_ID

    I can run the following query to get those courses a particular student is enrolled in (which is fine):

    mysql> SELECT E.SCHEDULE_ID,SC.COURSE_ID FROM ENROLL AS E,SCHEDULED_COURSES AS SC WHERE E.COMPANY_ID=1 AND E.STUDENT_ID=50 AND E.SCHEDULE_ID=SC.SCHEDULE_ID;
    +-------------+-----------+
    | SCHEDULE_ID | COURSE_ID |
    +-------------+-----------+
    | 21 | 46 |
    | 17 | 39 |
    | 18 | 41 |
    | 27 | 54 |
    +-------------+-----------+

    Now, what I really need is a query statement that will show those courses this student is NOT enrolled in yet. I've tried everything under the sun, but nothing seems to work.

    Please advise,

    Alan

  3. #3
    Join Date
    Apr 2005
    Location
    Colorado Springs
    Posts
    1
    Try this:

    SELECT
    E.SCHEDULE_ID,
    SC.COURSE_ID
    FROM ENROLL AS E
    LEFT OUTER JOIN SCHEDULED_COURSES AS SC
    E.SCHEDULE_ID=SC.SCHEDULE_ID
    WHERE E.COMPANY_ID=1
    AND E.STUDENT_ID=50
    AND SC.SCHEDULE_ID IS NULL

    Sorry, I don't know how to duplicate the left outer join with legacy SQL.

  4. #4
    Join Date
    Nov 2004
    Location
    Huddinge, Sweden
    Posts
    283
    Code:
    SELECT SC.COURSE_ID 
    FROM SCHEDULED_COURSES AS SC 
    WHERE NOT EXISTS(SELECT * FROM ENROLLED AS E
    
    WHERE E.COMPANY_ID=1 AND E.STUDENT_ID=50 AND E.SCHEDULE_ID=SC.SCHEDULE_ID)
    This is the "purest" way to write this query, and it should be the fastest.

    An alternate way would be:
    Code:
    SELECT SC.COURSE_ID 
    FROM SCHEDULED_COURSES AS SC 
    WHERE SC.SCHEDULE_ID NOT IN (SELECT E.SCHEDULE_ID FROM ENROLLED AS E
    
    WHERE E.COMPANY_ID=1 AND E.STUDENT_ID=50)
    Rune
    If you hit a brick wall, you didn't jump high enough!

  5. #5
    Join Date
    Feb 2005
    Posts
    31
    Hi guys,

    If it's not one thing, its another. I tried all your suggestions and without exception I got an error for each query I tried. Here's the readout from the console:

    mysql> SELECT SC.COURSE_ID FROM SCHEDULED_COURSES AS SC WHERE NOT EXISTS(SELECT * FROM ENROLLED AS E WHERE E.COMPANY_ID=1 AND E.STUDENT_ID=50 AND E.SCHEDULE_ID=SC.SCHEDULE_ID);
    ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS(SELECT * FROM ENROLLED AS E WHERE E.COMPANY_ID=1 AND E.S
    mysql> SELECT SC.COURSE_ID FROM SCHEDULED_COURSES AS SC WHERE SC.SCHEDULE_ID NOT IN(SELECT E.SCHEDULE_ID FROM ENROLL AS E WHERE E.COMPANY_ID=1 AND E.STUDENT_ID=50);
    ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT E.SCHEDULE_ID FROM ENROLL AS E WHERE E.COMPANY_ID=1 AND
    mysql> SELECT E.SCHEDULE_ID,SC.COURSE_ID FROM ENROLL AS E LEFT OUTER JOIN SCHEDULED_COURSES AS SC E.SCHEDULE_ID=SC.SCHEDULE_ID WHERE E.COMPANY_ID=1 AND E.STUDENT_ID=50 AND SC.SCHEDULE_ID IS NULL;
    ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'E.SCHEDULE_ID=SC.SCHEDULE_ID WHERE E.COMPANY_ID=1 AND E.STUDENT

    I don't get it. I'm running MySQL version 4.0.14. These queries should work.

    Please advise,

    Alan

  6. #6
    Join Date
    Jan 2004
    Location
    Alexandria, VA
    Posts
    392
    How about these:

    For the first problem:

    Courses Table:
    COURSE_ID | SUBJECT_TITLE | DESCRIPTION

    Scheduled_Courses Table:
    SCHEDULE_ID | START_DATE | END_DATE | COURSE_ID | INSTRUCTOR_ID

    Enroll Table:
    SCHEDULE_ID | ENROLL_DATE | STUDENT_ID|


    Code:
    Select Enroll.Schedule_ID, Courses_Subject_Title
    From Enroll, Scheduled_Courses, Courses
    Where Enroll.StudentID = 123
    And Scheduled_Courses.Schedule_ID = Enroll.ScheduleID
    And Courses.Course_ID = Scheduled_Courses.Course_ID

    For the second Problem:

    ENROLL table:
    ENROLL_DATE | STUDENT_ID | SCHEDULE_ID

    SCHEDULED_COURSES table:
    SCHEDULE_ID | START_DATE | END_DATE | COURSE_ID | INSTRUCTOR_ID

    Code:
    Select Scheduled_Courses.Course_ID
    From Scheduled_Courses
    where Scheduled_Courses.Schedule_ID Not In (Select Schedule_ID from Enroll Where Student_ID = 123)
    Bob Rouse
    Dimension Data

  7. #7
    Join Date
    Jan 2004
    Location
    Alexandria, VA
    Posts
    392
    Note: Not familiar with MySQL, but the alias used in the previous examples may not work ("SELECT E.SCHEDULE_ID FROM ENROLLED AS E"...)
    Bob Rouse
    Dimension Data

  8. #8
    Join Date
    Feb 2005
    Posts
    31
    Thanks for the help Bob, but we're still batting zero. For both problems, I'm trying to find those courses a student is NOT enrolled in yet.

    Your answer to the first problem works but does just the opposite of what I want it to do. There seems to be a problem with MySQL accepting nested select statements on your second answer. I don't know if there really is a syntax error there or if my version of MySQL simply doesn't support nested select statements? Anyway, here's the readout from the console on your second answer:

    mysql> SELECT ENROLL.SCHEDULE_ID,COURSES.SUBJECT_TITLE FROM ENROLL,SCHEDULED_COU
    RSES,COURSES WHERE ENROLL.STUDENT_ID=50 AND SCHEDULED_COURSES.SCHEDULE_ID=ENROLL
    .SCHEDULE_ID AND COURSES.COURSE_ID=SCHEDULED_COURSES.COURSE_ID;
    +-------------+-------------------------------------+
    | SCHEDULE_ID | SUBJECT_TITLE |
    +-------------+-------------------------------------+
    | 21 | Core Servlets and Java Server Pages |
    | 17 | Biology101 |
    | 18 | Chemistry101 |
    | 27 | MySQL |
    | 30 | Music & Instrument Playing |
    +-------------+-------------------------------------+
    5 rows in set (0.32 sec)

    mysql> SELECT SCHEDULED_COURSES.COURSE_ID FROM SCHEDULED_COURSES WHERE SCHEDULED_COURSES.SCHEDULE_ID NOT IN (SELECT SCHEDULE_ID FROM ENROLL WHERE STUDENT_ID=50);
    ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp
    onds to your MySQL server version for the right syntax to use near 'SELECT SCHED
    ULE_ID FROM ENROLL WHERE STUDENT_ID=50)' at line 1

    Please advise,

    Alan

  9. #9
    Join Date
    Jan 2004
    Location
    Alexandria, VA
    Posts
    392
    MySQL DOES support nested SQL. It could be the mixed prefixing. Try these:

    Select Course_ID
    From Scheduled_Courses
    where Schedule_ID Not In (Select Schedule_ID from Enroll Where Student_ID = 123)

    -or-

    Select Scheduled_Courses.Course_ID
    From Scheduled_Courses
    where Scheduled_Courses.Schedule_ID Not In (Select Enroll.Schedule_ID from Enroll Where Enroll.Student_ID = 123)
    Bob Rouse
    Dimension Data

  10. #10
    Join Date
    Jan 2004
    Location
    Alexandria, VA
    Posts
    392
    As to the original problem, try:

    Code:
    Select Scheduled_Courses.Schedule_ID, Courses.Subject_Title
    From Scheduled_Courses, Courses
    Where Scheduled_Courses.Course_ID = Courses.Course_ID
    And Scheduled_Courses.Schedule_ID Not In (Select Enroll.Schedule_ID from Enroll Where Enroll.Student_ID = 123)
    Bob Rouse
    Dimension Data

  11. #11
    Join Date
    Jun 2004
    Location
    Houston area
    Posts
    557
    SELECT SCHEDULED_COURSES.SCHEDULE_ID, COURSES.SUBJECT_TITLE FROM SCHEDULED_COURSES INNER JOIN COURSES ON SCHEDULED_COURSES.COURSE_ID = COURSES.COURSE_ID WHERE SCHEDULED_COURSES.SCHEDULE_ID NOT IN (SELECT SCHEDULE_ID FROM ENROLL WHERE STUDENT_ID = 123)

    I think this should get what you're looking for.

    WHAT'S WITH ALL THE ALIASES? You've spent more time trying to use a shortcut than if you just used the table/field names!

    L
    Last edited by Laurel; 04-29-2005 at 12:31 PM.
    A balanced diet is a cookie in each hand.

  12. #12
    Join Date
    Feb 2005
    Posts
    31
    Ok, fellas. I don't know what's going on. You're approaches to the problem seen certainly fine to me, but there is definitely something screwy here. Someone asked why I used the aliases. That's because I discovered that previous attempts at formulating a query were lengthy and the console will only allow me to type so many characters at one time. So to shorten the query string I started using aliases.

    So, once again, no luck with your answers. They all seem to point to the same syntax error, that is, at the beginning of the inner select statement. Here is the console readout:

    mysql> SELECT COURSE_ID FROM SCHEDULED_COURSES WHERE SCHEDULE_ID NOT IN (SELECT SCHEDULE_ID FROM ENROLL WHERE STUDENT_ID=50);
    ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp
    onds to your MySQL server version for the right syntax to use near 'SELECT SCHED
    ULE_ID FROM ENROLL WHERE STUDENT_ID=50)' at line 1
    mysql> SELECT SCHEDULED_COURSES.COURSE_ID FROM SCHEDULED_COURSES WHERE SCHEDULED_COURSES.SCHEDULE_ID NOT IN (SELECT ENROLL.SCHEDULE_ID FROM ENROLL WHERE ENROLL.STUDENT_ID=50);
    ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp
    onds to your MySQL server version for the right syntax to use near 'SELECT ENROL
    L.SCHEDULE_ID FROM ENROLL WHERE ENROLL.STUDENT_ID=5
    mysql> SELECT SCHEDULED_COURSES.SCHEDULE_ID,COURSES.SUBJECT_TITLE FROM SCHEDULED_COURSES,COURSES WHERE SCHEDULED_COURSES.COURSE_ID=COURSES.COURSE_ID AND SCHEDULED_COURSES.SCHEDULE_ID NOT IN (SELECT ENROLL.SCHEDULE_ID FROM ENROLL WHERE ENROLL.STUDENT_ID=50);
    ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp
    onds to your MySQL server version for the right syntax to use near 'SELECT ENROL
    L.SCHEDULE_ID FROM ENROLL WHERE ENROLL.STUDENT_ID=5
    mysql> SELECT SCHEDULED_COURSES.SCHEDULE_ID,COURSES.SUBJECT_TITLE FROM SCHEDULED_COURSES INNER JOIN COURSES ON SCHEDULED_COURSES.COURSE_ID=COURSES.COURSE_ID WHERE SCHEDULED_COURSES.SCHEDULE_ID NOT IN (SELECT SCHEDULE_ID FROM ENROLL WHERE STUDENT_ID=50);
    ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp
    onds to your MySQL server version for the right syntax to use near 'SELECT SCHED
    ULE_ID FROM ENROLL WHERE STUDENT_ID=50)' at line 1

    Any idea what's going on?

    Alan

  13. #13
    Join Date
    Jun 2004
    Location
    Houston area
    Posts
    557
    In defense of MY code:

    Do you have the STUDENT_ID field defined as a NUMBER or as TEXT in your table? If it's a text field, you will have to put 50 in single quotes in your SQL!

    The way you have it now, you're passing a NUMBER to what I'm guessing is a text field. Ya can't do that! Likewise, you can't pass text to a number field, so, if your user enters 50 in a textbox, you will have to use the VAL function like this:

    (SELECT SCHEDULE_ID FROM ENROLL WHERE STUDENT_ID=VAL(50));

    SO THERE! :-

    L
    Last edited by Laurel; 05-01-2005 at 04:40 PM.
    A balanced diet is a cookie in each hand.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center