#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    6
    Rep Power
    0

    MySQL Query Help


    Hi All,

    Wondering in anyone can assist me with a couple of queries which have cropped up in a project. The project is a database of people and their attendance of training courses.

    There are a few tables...

    Users (with UserID and Username)

    Teachers (with TeacherID and TeacherName)

    Courses (with CourseID, CourseName, CourseDate and TeacherID) - this one relates to which teacher took which course

    Attendances (with UserID and CourseID) - this one relates which user attended which course

    What the client is after is firstly selecting all the users who have NOT attended a specific teachers' courses. In other words, if the have been on a course with the specified teacher, they will not be shown.

    Secondly, the client wants the same query as the first but also limiting it to the last 6 months. In other words, selecting all the users who have NOT attended a specific teachers' courses within the last six months.

    This one has got me a bit baffled so any assistance would be appreciated.

    Rik
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,201
    Rep Power
    4279
    LEFT OUTER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    6
    Rep Power
    0
    Thanks for that but any other tips? I'm afraid I'm a bit out of my depth with this one and the client is really coming down on me! Any assistance appreciated.

    Rik
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,201
    Rep Power
    4279
    Originally Posted by RikStryker
    ... the client is really coming down on me!
    you will please forgive me for believing that this is actually a homework assignment for some course

    Comments on this post

    • paulh1983 agrees : i am inclined to believe you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    you will please forgive me for believing that this is actually a homework assignment for some course
    Actually it's a favour for a friends' new business. Everything is done apart from this darn query which has got me banging my head against the wall!
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    6
    Rep Power
    0
    Thanks for the suggestions. Obviously I am now using the proper database and column names. I've currently got...

    Code:
    SELECT DISTINCT * FROM users INNER JOIN attendances ON database.id = attendances.RespondentID LEFT JOIN events ON attendances.EventID = events.EventID AND events.ModID = 57 WHERE events.EventID IS NULL GROUP BY users.id ORDER BY `date_updated` desc LIMIT 0,50
    Which seems to be working apart from one thing. If a user has attended an event by the particular moderator and an another event by another moderator, it still appears on the list of result. I.e. if they attended an event by ModID=57, they should be removed from the list regardless of if they have attended an event with another ModID.

    This is getting curiouser and curiouser!

    Rik
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,201
    Rep Power
    4279
    Originally Posted by RikStryker
    Obviously I am now using the proper database and column names.
    obviously not quite
    Code:
    SELECT DISTINCT * 
      FROM users 
    INNER 
      JOIN attendances 
        ON database.id = attendances.RespondentID 
    LEFT 
      JOIN events 
        ON attendances.EventID = events.EventID
       AND events.ModID = 57 
     WHERE events.EventID IS NULL 
    GROUP 
        BY users.id 
    ORDER 
        BY date_updated DESC LIMIT 0,50
    also, you will not need to use that DISTINCT if you have GROUP BY users.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    6
    Rep Power
    0
    Thanks again!

    Rik

IMN logo majestic logo threadwatch logo seochat tools logo