Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2011
    Posts
    5

    Unanswered: SQL Query help needed..

    I am using Visual Studio 2008 to build an ASP.net / VB.net based website. I am connecting to an Access Database.

    Database tables:
    Course(DegreeID, ModuleID, Level, Compulsory)
    Modules(ModuleID, Module_Name, Points, Specification, Semester, State)
    Enrolment(StudentID, ModuleID, Level, Enrolment_Year, Module_CATs)


    Here is the original query I had written and used it to generate the modules available to a student depending on their course and level:

    SELECT Modules.ModuleID, Modules.Module_Name, Modules.Points, Course.Compulsory
    FROM Modules, Course
    WHERE Course.DegreeID = 'variable' AND Course.Level = 'variable' AND Course.ModuleID = Modules.ModuleID


    ^This worked and displayed the information I wanted.


    Now I want to expand this query to only display the above information for the modules the student is enrolled for. I wrote the query below but it isn't working:


    SELECT Modules.ModuleID, Modules.Module_Name, Modules.Points, Course.Compulsory
    FROM Modules, Course
    WHERE Course.DegreeID = 'variable' AND Course.Level = 'variable' AND Course.ModuleID = Modules.ModuleID
    LEFT JOIN Enrolment ON Enrolment.ModuleID = Course.ModuleID AND Enrolment.StudentID = 'variable'



    I have tried other ways but cannot get it to work, the above query was my final effort and I'm surprised it didn't work!

    I then want to display the above information for when a student is not enrolled in a module, I am assuming I just change the equals in this: 'LEFT JOIN Enrolment ON Enrolment.ModuleID = Course.ModuleID' to != for not equal? But as my above query doesn't work I can't test this idea.

    Help please.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by smokinmonkey View Post
    ... I'm surprised it didn't work!
    i'm not

    you've got a WHERE clause smack in the middle of your FROM clause

    move it after the FROM clause and see what happens

    hint: the FROM clause includes all joins

    bonus tip: don't use comma-style joins, especially not mixed with explicit JOINs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011
    Posts
    5
    Quote Originally Posted by r937 View Post
    i'm not

    you've got a WHERE clause smack in the middle of your FROM clause

    move it after the FROM clause and see what happens

    hint: the FROM clause includes all joins

    bonus tip: don't use comma-style joins, especially not mixed with explicit JOINs
    Thank you!

    I don't know why but I completely forgot the comma was joining the tables, I got rid of the left join and just added enrolment with a comma join. Then my WHERE clause returned what I needed.

  4. #4
    Join Date
    Apr 2011
    Posts
    5
    Here is the code that worked:

    SELECT [Modules].[ModuleID], [Modules].[Module_Name], [Modules].[Points], [Course].[Compulsory]
    FROM [Modules], [Course], [Enrolment]
    WHERE [Enrolment].[ModuleID] = [Course].[ModuleID] AND ([Course].[DegreeID] = ?) AND ([Course].[Level] = ?) AND ([Course].[ModuleID] = [Modules].[ModuleID]) AND ([Enrolment].[StudentID] = ?)



    This returns the modules that a student is enrolled for.

    How can I return rows that are NOT in the enrolment table but still meet the rest of the requirements from the WHERE clause.

    When I changed '[Enrolment].[ModuleID] = [Course].[ModuleID]' to not equal to it didn't have the desired affect.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by smokinmonkey View Post
    Here is the code that worked:
    here, allow me to clean that up a bit for you
    Code:
    SELECT modules.moduleid
         , modules.module_name
         , modules.points
         , course.compulsory
      FROM enrolment
    INNER
      JOIN course
        ON course.moduleid = enrolment.moduleid
       AND course.degreeid = ? 
       AND course.level = ? 
    INNER
      JOIN modules
        ON modules.moduleid = course.moduleid
     WHERE enrolment.studentid = ?
    How can I return rows that are NOT in the enrolment table
    pretty hard to return something that isn't there

    could you maybe rephrase your request?

    it's gonna involve a LEFT OUTER JOIN somewhere, which is why i took the liberty of writing your joins using explicit JOIN syntax, they'll be easier to change
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2011
    Posts
    5
    Quote Originally Posted by r937 View Post
    here, allow me to clean that up a bit for you
    Code:
    SELECT modules.moduleid
         , modules.module_name
         , modules.points
         , course.compulsory
      FROM enrolment
    INNER
      JOIN course
        ON course.moduleid = enrolment.moduleid
       AND course.degreeid = ? 
       AND course.level = ? 
    INNER
      JOIN modules
        ON modules.moduleid = course.moduleid
     WHERE enrolment.studentid = ?
    pretty hard to return something that isn't there

    could you maybe rephrase your request?

    it's gonna involve a LEFT OUTER JOIN somewhere, which is why i took the liberty of writing your joins using explicit JOIN syntax, they'll be easier to change
    Thanks for the code clean up.


    I want the same critera as the first query only I don't want the ModuleID to be found in the Enrolment table.

    I tried the same query with a not equal to operator but it returned the wrong data.

    I have been looking at 'WHERE NOT IN' syntax but I am unsure how to implement it.


    Edit:

    Outer Join returns results when no items match? So... ?

    Code:
    SELECT modules.moduleid
         , modules.module_name
         , modules.points
         , course.compulsory
      FROM enrolment
    LEFT OUTER
      JOIN course
        ON course.moduleid != enrolment.moduleid
       AND course.degreeid = ? 
       AND course.level = ? 
    INNER
      JOIN modules
        ON modules.moduleid = course.moduleid
     WHERE enrolment.studentid = ?
    I can't try this from work but will test first thing tomorrow.
    Last edited by smokinmonkey; 04-11-11 at 20:23.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by smokinmonkey View Post
    ...I don't want the ModuleID to be found in the Enrolment table.
    so, like this...?
    Code:
    SELECT modules.moduleid
         , modules.module_name
         , modules.points
         , course.compulsory
      FROM enrolment
    INNER
      JOIN course
        ON course.moduleid = enrolment.moduleid
       AND course.degreeid = ? 
       AND course.level = ? 
    LEFT OUTER
      JOIN modules
        ON modules.moduleid = enrolment.moduleid
     WHERE enrolment.studentid = ?
       AND modules.moduleid IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2011
    Posts
    5
    Haha I edit and you reply.

    I will test it tomorrow. Thanks for your help.

Posting Permissions

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